How do I detect execute permission granted to a role (when no ON clause was used)

Question

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.

Answer 1

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'

Answer 2

To complement the answer by Hannah Vernon:

SELECT *
FROM sys.objects
WHERE object_id IN (
         SELECT major_id
         FROM 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

Answer 3

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!