Azure SQL – is it possible to assign CREATE PROCEDURE permission for the master database

azure-sql-database

I am trying to add a stored procedure to the master database (I deliberately am trying this for the master db) on Azure SQL. Is this possible? I suspect the answer is "no", but I'd like to see some confirming documentation on it if that is the case.

What I've tried so far:

Here's the procedure (just as a test):

CREATE PROCEDURE dbo.blah AS RETURN 0;

I've tried the above using the admin login, and it failed:

Msg 262, Level 14, State 18, Procedure blah, Line 2 [Batch Start Line 21]
CREATE PROCEDURE permission denied in database 'master'.

I then tried to create another login and user and then assigned them to the "dbmanager" role and tried the create procedure again – same failure.

Best Answer

Not if you are using Azure DB. Probably if you use Azure Instance.

https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#security

Related Solutions

Sql-server – Creating SQL Azure login for single database

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '&lt;strong password here&gt;';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;

EXEC sp_addrolemember 'db_owner', 'YourNewUser';

Running Brent Ozar’s sp_BlitzIndex on Azure SQL Database

Kendra here (the author of sp_BlitzIndex)

First, thanks for being interested in the procedure and trying it out.

Azure doesn't expose all the dynamic management views that we get in the boxed product. I do actually reference sys.dm_db_partition_stats, but there's other info I need to get from sys.partitions for other users. (Is it using compression? What type?)

I haven't had time to write an test an Azure specific version, just because I haven't had a lot of demand for it. But your question does tell me there's more interest than I knew of -- so there's that!

I will at minimum look at implementing some error handling so it lets you know in a graceful way though. (This is a bit of a moving target, because Azure has been expanding the features that are available to users as well.)

Best Answer

Related Solutions

Sql-server – Creating SQL Azure login for single database

Running Brent Ozar’s sp_BlitzIndex on Azure SQL Database

Related Question