How do I detect execute permission granted to a role (when no ON clause was used) 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:


How do I determine using TSQL what roles are granted execute permissions on a specific stored procedure?
List all permissions for a given role?
can exec permissions be granted to a role in SQL Server 2008 / 2012


I'm looking for the most reliable way of identifying this method of granting (or denying) privileges.
GRANT EXECUTE TO [principal] is simply a shortcut for GRANT EXECUTE ON DATABASE::&lt;dbname&gt; 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'