Sql-server – How to detect execute permission granted to a role (when no ON clause was used)

permissionsrolesql serversql-server-2012

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.

Best Answer

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'
Related Question