Permissions on a Azure SQL DB

Question

I have granted CREATE PROCEDURE permissions to User DW_Test_USER on DB dwprddb without error. However, when I attempt to create a strored proc in the dwprddb while signed in as DW_Test_USER I receive an error indicating I do not have permissions to create a stored proc. What am I missing?

Used to grant permissions:
GRANT CREATE PROCEDURE ON DATABASE::dwprddb TO [DW_Temp_USER]

Used to review permissions:
SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc,
pr.authentication_type_desc, pe.state_desc, pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
Order by name

Results of review query:
17 DW_Temp_USER SQL_USER INSTANCE GRANT ALTER
17 DW_Temp_USER SQL_USER INSTANCE GRANT CONNECT

Error when attempting to Create Stored Proc in dwprddb:
Msg 262, Level 14, State 18, Line 7
CREATE PROCEDURE permission denied in database 'dwprddb'.

Why is the GRANT CREATE PROCEDURE statement successful but the CREATE PROCEDURE permission does not appear to be in place?

Answer 1

Hello,

I believe your question may have resolved my issue! I am logged in using SQL Authentication as a DW_Temp_USER when I should be connected with my DBA credentials using Azure Active Directory - Universal with MFA. I will attempt this process again under the proper User with the credentials that should allow me to do this.

I will respond later to let you know how it worked out.

Thank you!!

Answer 2

Hi @Hackl, Scott ,

Thanks for using Microsoft Q&A !!
How are you logging to SQL Server instance. Is it SQL Authentication ?

Thanks
Saurabh