SQL Server – GRANT EXECUTE on Stored Procedures

permissionssql serverstored-procedures

I have a role ExecSP which I use to manage who can execute stored procedures. EXECUTE permission is granted on all SPs to this role.

I also use SQL Server Data Tools (SSDT) to manage MS SQL Server database schema.
In the file where my SP is defined, I have this:

CREATE PROC SomeProc AS
.
.
.

GO

GRANT EXECUTE ON SomeProc TO ExecSP

However, when I apply the change to the target database, and do the schema comparison, it reports differences in SP permissions, where on the DB side I have this:

GRANT EXECUTE ON SomeProc TO ExecSP AS dbo

I've tried to understand what AS dbo part represents, but I couldn't.

My questions:

Does it mean that the SP will be executed with dbo rights, regardless of who is executing it? (I guess/hope it doesn't.)
Why is this added automatically?
I want this SP to be executed with the rights the caller has – how do I achieve that?

Best Answer

Use the AS principal clause to indicate that the principal recorded as the grantor of the permission should be a principal other than the person executing the statement. For example, presume that user Mary is principal_id 12 and user Raul is principal 15. Mary executes GRANT SELECT ON OBJECT::X TO Steven WITH GRANT OPTION AS Raul; Now the sys.database_permissions table will indicate that the grantor_prinicpal_id was 15 (Raul) even though the statement was actually executed by user 13 (Mary).

That is wrong. Try to reproduce it and you'll get the error:

Msg 15151, Level 16, State 1, Line 1 Cannot find the object 'X', because it does not exist or you do not have permission.

Even if Mary and Raul are members of sysadmin fixed server role. And that is why: the AS clause is not intended to be used with WITH GRANT OPTION, but, as BOL 2008R2 offline version says:

AS principal Specifies a principal from which the principal executing this query derives its right to grant the permission

That is, the mentioned above repro with Mary and Raul both sysadmins fails because Raul has no explicit permission on object X, and even if it has no need to be granted an explicit permission being sysadmin, this time we get an error because the permission check is not bypassed: server checks the sys.database_permissions to find the appropriate permission.

The AS clause is used in following scenario: we have a database role MyRole that is granted some permission WITH GRANT OPTION. Let's Mary be a user-member of MyRole. Now Mary wants to give the permission she has to Steven, but the simple

GRANT SELECT ON OBJECT::X TO Steven

failed because as a simple user Mary has no permission on X, but if she writes

GRANT SELECT ON OBJECT::X TO Steven as MyRole

it works fine: there is the explicit grant to MyRole is sys.database_permissions

Returning to your question, if you try now to script any of your object using Management Studio (database -> tasks -> generate scripts, select some objects and in "advanced" turn on "script object level permissions"), you'll get the same script: GRANT ... AS DBO. And only in the case with the role you'll see GRANT .. AS MYROLE. So the Studio always scripts out the grantor from sys.database_permissions, but it's not an "audit", you'll never see other principals that are not dbo except for the role case, and even if you has a principal that has only CONTROL permission on only one object, when he grants someone a permission to that object, in sys.database_permissions the grantor will be dbo.

Related Solutions

Sql-server – SQL Server – DBO without Backup

Grant the user SELECT, INSERT, UPDATE, DELETE, EXECUTE and VIEW DEFINITION rights to the database (or the schema). This will grant the user the rights to all the objects within the database (or the schema).

You'll probably also need to give them rights like ALTER ANY OBJECT within the database as well so that they can modify the objects.

Sql-server – Bulk grant execute permissions for scalar functions & stored procedures

This bit of nested dynamic SQL grants exec or select on all the various procedure and function types, that were not shipped by MS, and that exist in any database outside of master, msdb, tempdb and model. It is certainly not easy on the eyes, tedious to reverse engineer, and I haven't thoroughly tested it, but hopefully if you work through it you can figure out what I've done. ALl you really need to specify is the name of the login you want to grant the rights to - of course this assumes that the login has been mapped to a database user with the same name in each database. That was a layer of complexity I wasn't prepared to add, sorry. :-)

DECLARE 
  @principal SYSNAME = N'some_server_principal';


DECLARE 
  @sql1 NVARCHAR(MAX) = N'', 
  @sql2 NVARCHAR(MAX) = N'',
  @sql3 NVARCHAR(MAX) = N'';

SELECT @sql1 += N'
SELECT @sql += N''SELECT @sql += N''''
USE ' + QUOTENAME(name) + ';
GRANT '''' 
  + CASE WHEN type IN (''''P'''',''''PC'''') THEN ''''EXEC'''' ELSE ''''SELECT'''' END 
  + '''' ON '''' + QUOTENAME(SCHEMA_NAME(schema_id)) + ''''.'''' + QUOTENAME(name) 
  + '''' TO ' + QUOTENAME(@principal) + ';'''' FROM ' + QUOTENAME(name) + '.sys.objects
  WHERE is_ms_shipped = 0 AND type IN (''''P'''',''''PC'''',
    ''''FN'''',''''AF'''',''''FN'''',''''TF'''',''''IF'''');'';'
  FROM sys.databases
  WHERE database_id > 4;

EXEC sp_executesql @sql1, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql2 OUTPUT;

EXEC sp_executesql @sql2, N'@sql NVARCHAR(MAX) OUTPUT', @sql = @sql3 OUTPUT;

-- you won't be able to validate all of this, since Management Studio will
-- truncate the output quietly. But you should get a good sense of what the
-- end result will be:

SELECT @sql3;

-- When you are happy with the above, uncomment this:

-- EXEC sp_executesql @sql3;

This is kind of like Inception. In order to generate the right output, you have to figure out which layer every element is on. This is why the initial string is so ugly, and littered with escaped apostrophes in sets of 2 and 4. It can be a little unnerving at first, but you can become adept very quickly at writing dynamic SQL for metadata purposes.

Best Answer

Related Solutions

Sql-server – SQL Server – DBO without Backup

Sql-server – Bulk grant execute permissions for scalar functions & stored procedures

Related Question