Permission hierarchy vs. WITH GRANT OPTION

Question

I have an Azure SQL database with this security setup:

• SchemaUpdater user with db_ddladmin, db_datawriter, and db_datareader roles. The user is used to execute database change scripts during application deployment.
• App user with db_datawriter, and db_datareader roles. The user is used by the application to work with the data.

I wanted the app user to also execute a stored procedure, say dbo.sp_MyProc. I did following:

1. grant execute to [SchemaUpdater] with grant option using an admin account, so that change scripts can grant permissions for any SPs.
2. Created the stored procedure in a change script.
3. Tried to grant execute on [dbo].[sp_MyProc] to [App] in the same change script. That didn't work.

The step failed with

Cannot find the object 'sp_MyProc', because it does not exist or you do not have permission.

What's interesting, that if the SchemaUpdater does grant execute to [App] instead, with no individual SP mentioned, it works.

My questions are:

1. Why can't SchemaUpdater grant a permission on a single SP, but can grant it on all SPs?
2. Is SchemaUpdater missing some other permission, so that it could grant permissions to individual SPs?

Answer 1

Unfortunately the WITH GRANT permission is not inherited in some, or perhaps all, cases. I couldn't find anything in the documentation or other articles on the [cue thunder] World Wide Web [stop thunder] stating that, though. I am with you in that I would expect it to be inherited.

I tested this on SQL Server 2016 and got the same results as you did. There are several options to work around it, but the most fine-grained option would be to assign EXECUTE WITH GRANT to SchemaUpdater on each stored procedure.

GRANT EXECUTE ON [dbo].[sp_MyProc] TO [SchemaUpdater] WITH GRANT OPTION

I have confirmed that WITH GRANT is not inherited on other permissions and objects as well. I tested the same scenario with VIEW DEFINITION on a table, and had to explicitly GRANT WITH GRANT on the table before SchemaUpdater could grant that permission to another user.

So the specific answers to your questions:

1. Since WITH GRANT is not inherited, a user must have explicit WITH GRANT permission on an object in order to grant permissions. Since SchemaUpdater has EXECUTE WITH GRANT on the database, it can grant EXECUTE on the database. Since it doesn't have an explicit EXECUTE WITH GRANT on the stored procedure, it cannot grant explicit permissions on that stored procedure.

2. As mentioned, SchemaUpdater will need EXECUTE WITH GRANT explicitly on each stored procedure, or a higher privilege that includes that permission (such as db_owner).

Answer 2

WITH GRANT OPTION predates the enhanced authorization model introduced in SQL 2005, which enabled database-level and schema-level grants. But even if they were designed at the same time, I doubt this would have been enabled.

Consider, for instance, if the grantee of a database-level permission WITH GRANT OPTION could grant the same permission at the object level, what would happen when you revoke the grantee's permission?

EG

USE tempdb

go
CREATE proc foo2
as
select 1 a
go

create user joe without login
create user alice without login
go
grant execute to joe with grant option

execute as user = 'joe'
    grant execute on foo to alice
revert 

go

revoke execute to joe cascade

go

execute as user='alice'
  exec foo2 
revert

SQL would have to keep track that alice's execute permission was not just granted by joe, which it does, but that it was granted because of joe's schema-level or database-level permission. And considering that joe might have database-level, schema-level, and object-level permissions to the object, that starts to sound both complicated to implement and understand.