Permission hierarchy vs. WITH GRANT OPTION

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).

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.

For some reason this old thread came up again. So here my 2 cents: First of all, merely being a member of db_ddladmin does not allow granting permissions on the whole database. In general, it is possible to grant permissions on objects owned. So for example, once SchemaUpdater creates a new schema and table it can grant permissions on those to others. So something must be missing in the description of the setup here.

In general, I do advise against using the WITH GRANT OPTION, because admins then lose control over who else can grant permissions, since this option can be passed on infinitely.

Here you can read up more on this and the alternative using roles:

Delegating permission management using Roles vs WITH GRANT OPTION