3

I just spent hours trying to figure out how an app had privilege to execute stored procedures.

TIL from StackExchange: Turns out the ON clause is optional for GRANT, allowing a role to be granted execute on everything. I had to laugh at the very bottom of this MSDN page after I had tried my best to validate the syntax.

GRANT Object Permissions (Transact-SQL)

CREATE ROLE Test
GRANT EXECUTE TO Test

Ok, so that's cool and all (not very principle-of-least-privilege-esque).

However: How am I to discover or detect that grant?

I tried looking through all the SQL Server Management Studio login, role, user, and schema privilege GUIs and don't see this type of Grant identified anywhere.

While debugging, I found questions about enumerating privileges in a query and while those are related, this scenario is neither mentioned nor covered by the answers there:

I'm looking for the most reliable way of identifying this method of granting (or denying) privileges.

4

GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::<dbname> TO [principal];

You can check this using the following:

SELECT dp.name
    , perms.class_desc
    , perms.permission_name
    , perms.state_desc
FROM sys.database_permissions perms
    INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id 
WHERE dp.name = 'MyRole'
2

I wasn't paying attention to, or correctly interpreting, the results of the query from Kin's answer to this question:

List all permissions for a given role?

In the results of the query, you'll see execute listed but with no specific object type or object name!

0

To complement the answer by Max Vernon

select * from sys.objects where object_id in 
    (SELECT major_id sys.database_permissions perms
    INNER JOIN sys.database_principals dp ON perms.grantee_principal_id = dp.principal_id 
WHERE dp.name = 'ProdConfigUsers'
) and name like '%%'
order by create_date desc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.