Sql-server – Permission hierarchy vs. WITH GRANT OPTION

azure-sql-databasepermissionssql server

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:

grant execute to [SchemaUpdater] with grant option using an admin account, so that change scripts can grant permissions for any SPs.
Created the stored procedure in a change script.
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:

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

Best Answer

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:

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

Related Solutions

Sql-server – User can alter procedures without permission

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE 
(
  dp.type <> 'R' 
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY 
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;

SQL Server – How to Update Permission for Table Variable

Martin Smith's comment ("Does the subquery itself work?") was ultimately the answer and highlighted my insufficient forensic work! ;-)

The subquery was returning ROW COUNTS from tables:

...
INNER JOIN sys.dm_db_partition_stats AS ddps 
ON i.OBJECT_ID = ddps.OBJECT_ID
...

The fixed roles I was using don't give VIEW DATABASE STATE permission (which is required to view the db_partition_stats table). So, I'll either need to mess with the roles for these users or find another workaround.

I guess it's worth noting that if you come across this circumstance, you need to deconstruct your query. The fixed db roles that I refer to CAN CREATE, INSERT, UPDATE, and DELETE records from a table variable.

Best Answer

Related Solutions

Sql-server – User can alter procedures without permission

SQL Server – How to Update Permission for Table Variable

Related Question