Azure SQL Server user roles

Question

I have two servers on my Azure subscriptions that I use for SQL Server dbs with my adf work

I have initially created both of them, and created the same user id to use with both - one server for dev, another one for prod. As far as I remember, both of the users were created in the same way .. but .. it is only my recollection.

Now on the dev server I created the user defined type in the database "DBName" - a table type, and a stored procedure where it is used. Everything works OK Then, when I attempt to do the same on the prod, using scripts, I am able to create the type, and see it in the object explorer. However, when creating stored procedure, I get this error

Msg 15151, Level 16, State 1, Procedure myStoredProcedure, Line 2 [Batch Start Line 5] Cannot find the type 'MyTypeTBL', because it does not exist or you do not have permission.

Is it a user role causing this on the prod server? Is there way to compare the user roles between the two servers to make sure they are the same? I have a datafactory pipelines running in prod, using that user successfully for running stored procedures and selecting data. I was also creating tables and stored procedures with that user. What can I do to resolve this issue?

Answer 1

I am not sure, why or how this is a case, but I resolved it this way:

I found a sql file where I was saving commands I ran, so I had the following

CREATE USER [azuser] 
WITH PASSWORD = 'myPasswd',
DEFAULT_SCHEMA = dbo; 

-- 2) - add user to role(s) in db 
ALTER ROLE db_datareader ADD MEMBER [azuser]; 
ALTER ROLE db_datawriter ADD MEMBER [azuser];
ALTER ROLE [db_accessadmin] ADD MEMBER [azuser];
ALTER ROLE [db_owner] ADD MEMBER [azuser];

That means, I ran those commands before when initiating everything I added the following and got the problem resolved.

ALTER ROLE [db_ddladmin] ADD MEMBER [azuser];
-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'azuser'
GO

GRANT EXECUTE ON OBJECT::[dbo].[MyTypeTBL]
TO azuser;  
GO