Pre-checking execute privs for SPs We have a number of DBs where EXECUTE privs are required on a SP by SP basic. We have other DBs where we don't need to do this at all.

In the latter case, I understand that this is a blanket GRANT, but is there anyway to tell up front whether the blanket grant has been applied? For the blanket grant I don't see any users set up under the Permissions page in SSMS for any of the SPs in question and yet it just works. 



If there are no users here I don't know whether I need to specify users or assume a blanket grant.
Is there anyway to tell up front whether the blanket grant has been
  applied? For the blanket grant I don't see any users set up under the
  Permissions page in SSMS for any of the SPs in question and yet it
  just works.


Below is a TSQL method to find all explicit DB object permissions and members of fixed or custom DB roles per each DB you run it against. Just search the results for the value of the role name you need to confirm the user is a member of beforehand and that should suffice for your need.

/*
Script DB Level Permissions v2.1
Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/
DECLARE @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR
FOR
/*********************************************/
/*********   DB CONTEXT STATEMENT    *********/
/*********************************************/
SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --]
    ,1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --]
    ,1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********     DB USER CREATION      *********/
/*********************************************/
SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --]
    ,3 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --]
    ,4 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_principals AS rm
WHERE [type] IN (
        'U'
        ,'S'
        ,'G'
        ) -- windows users, sql users, windows groups

UNION

/*********************************************/
/*********    DB ROLE PERMISSIONS    *********/
/*********************************************/
SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --]
    ,5 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT 'EXEC sp_addrolemember @rolename =' + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --]
    ,6 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_role_members AS rm
WHERE USER_NAME(rm.member_principal_id) IN (
        --get user names on the database
        SELECT [name]
        FROM sys.database_principals
        WHERE [principal_id] &gt; 4 -- 0 to 4 are system users/schemas
            AND [type] IN (
                'G'
                ,'S'
                ,'U'
                ) -- S = SQL user, U = Windows user, G = Windows group
        )
--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********  OBJECT LEVEL PERMISSIONS *********/
/*********************************************/
SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,8 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE 
        WHEN PERM.STATE &lt;&gt; 'W'
            THEN PERM.state_desc
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.NAME) --select, execute, etc on specific objects
    + CASE 
        WHEN cl.column_id IS NULL
            THEN SPACE(0)
        ELSE '(' + QUOTENAME(cl.NAME) + ')'
        END + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default + CASE 
        WHEN PERM.STATE &lt;&gt; 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,9 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.objects AS obj ON PERM.major_id = obj.[object_id]
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
LEFT JOIN sys.columns AS cl ON cl.column_id = PERM.minor_id
    AND cl.[object_id] = PERM.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/
/*********    DB LEVEL PERMISSIONS   *********/
/*********************************************/
SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,11 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE 
        WHEN PERM.STATE &lt;&gt; 'W'
            THEN PERM.state_desc --W=Grant With Grant Option
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO &lt;user name&gt;
    + CASE 
        WHEN PERM.STATE &lt;&gt; 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,12 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.database_principals AS usr ON PERM.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser
WHERE [perm].[major_id] = 0
    AND [usr].[principal_id] &gt; 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN (
        'G'
        ,'S'
        ,'U'
        ) -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --]
    ,13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --]
    ,14 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT CASE 
        WHEN PERM.STATE &lt;&gt; 'W'
            THEN PERM.state_desc --W=Grant With Grant Option
        ELSE 'GRANT'
        END + SPACE(1) + PERM.permission_name --CONNECT, etc
    + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO &lt;user name&gt;
    + QUOTENAME(SCHEMA_NAME(major_id)) + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default + CASE 
        WHEN PERM.STATE &lt;&gt; 'W'
            THEN SPACE(0)
        ELSE SPACE(1) + 'WITH GRANT OPTION'
        END AS [-- SQL STATEMENTS --]
    ,15 AS [-- RESULT ORDER HOLDER --]
FROM sys.database_permissions AS PERM
INNER JOIN sys.schemas s ON PERM.major_id = s.schema_id
INNER JOIN sys.database_principals dbprin ON PERM.grantee_principal_id = dbprin.principal_id
WHERE class = 3 --class 3 = schema
ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp

FETCH NEXT
FROM tmp
INTO @sql
    ,@sort

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @sql

    FETCH NEXT
    FROM tmp
    INTO @sql
        ,@sort
END

CLOSE tmp

DEALLOCATE tmp


Source