Question

gen1 on Mon, 21 Dec 2015 20:35:42


At my work we've been asked to place this at the end of every sproc.

grant execute on [dbo].[sp_the_sproc] to [sproc_exec] as [dbo]

I'd like to ask the community, what difference does it make to add "as dbo" on this line?

Thanks!

gen


Sponsored



Replies

bhanu_nz on Mon, 21 Dec 2015 20:54:29


Hi,

AS <database_principal>

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

Source:https://msdn.microsoft.com/en-us/library/ms188371.aspx?f=255&MSPPError=-2147217396


Hope this helps

Ta

Bhanu






bhanu_nz on Mon, 21 Dec 2015 21:54:18


I'd like to ask the community, what difference does it make to add "as dbo" on this line?

I have tested this using few scenarios where the stored proc has Truncate & dynamic sql statements inside it. This didn't made any difference to the SP execution. Stored proc failed to execute sighting no enough permissions.

So I think there won't be any advantage or difference by adding as [dbo] to the GRANT statement.

Hope this helps

Thanks

Bhanu  


Erland Sommarskog on Mon, 21 Dec 2015 22:18:41


Most likely it is redundant.
What is is all about is a very obsucure option with permissions in SQL (which I believe is drawn from the ANSI standard). You can grant someone permission and also grant that person to grant that permission further. If you revoke permission from a user, all the users he has granted permission also lose their permission.

Furthermore, when you grant a user A permission, you can pretend that you are user B, which then has the same effect if you revoke permission from user B as above. The script below illustrates.

Since you presumably are dbo or member of db_owner when you grant permission, adding "AS dbo" is redundant, because that will apply anyway. (The one exception would be that you are not member of db_owner, but has permission to impersonate dbo. But that would be crazy.)

And the whole WITH GRANT OPTION and AS user is obscure that there is little reason to pay attention to it.

Script to illustrate WITH GRANT OPTION and AS user:

CREATE USER pelle WITHOUT LOGIN
CREATE USER stina WITHOUT LOGIN
CREATE TABLE jocke (a int NOT NULL)
go
GRANT SELECT ON jocke TO pelle WITH GRANT OPTION
go
GRANT SELECT ON jocke TO stina AS pelle
go
EXECUTE AS USER = 'stina'
PRINT 'Stina has permission to SELECT jocke'
SELECT a FROM jocke
REVERT
go
PRINT 'revoking SELECT on jocke FROM pelle'
REVOKE SELECT ON jocke FROM pelle CASCADE
go
EXECUTE AS USER = 'stina'
PRINT 'Stina can no longer access jocke'
SELECT a FROM jocke
REVERT
go
DROP USER stina
DROP USER pelle
DROP TABLE jocke