Get answers from your peers along with millions of IT pros who visit Spiceworks.
Join Now

--Verified to see all user access DB/Obj/Schema/User roles

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] > 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 <> '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 <> '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 <> '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 <user name>
    + CASE
            WHEN perm.state <> '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] > 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 <> '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 <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> '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

------------------
--Object Level Access

SELECT CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'GRANT' ELSE P.state_desc END AS cmd_state,
P.permission_name,
'ON '+ CASE P.class_desc
WHEN 'DATABASE' THEN 'DATABASE::'+QUOTENAME(DB_NAME())
WHEN 'SCHEMA' THEN 'SCHEMA::'+QUOTENAME(S.name)
WHEN 'OBJECT_OR_COLUMN' THEN 'OBJECT::'+QUOTENAME(OS.name)+'.'+QUOTENAME(O.name)+
CASE WHEN P.minor_id <> 0 THEN '('+QUOTENAME(C.name)+')' ELSE '' END
WHEN 'DATABASE_PRINCIPAL' THEN
CASE PR.type_desc
WHEN 'SQL_USER' THEN 'USER'
WHEN 'DATABASE_ROLE' THEN 'ROLE'
WHEN 'APPLICATION_ROLE' THEN 'APPLICATION ROLE'
END +'::'+QUOTENAME(PR.name)
WHEN 'ASSEMBLY' THEN 'ASSEMBLY::'+QUOTENAME(A.name)
WHEN 'TYPE' THEN 'TYPE::'+QUOTENAME(TS.name)+'.'+QUOTENAME(T.name)
WHEN 'XML_SCHEMA_COLLECTION' THEN 'XML SCHEMA COLLECTION::'+QUOTENAME(XSS.name)+'.'+QUOTENAME(XSC.name)
WHEN 'SERVICE_CONTRACT' THEN 'CONTRACT::'+QUOTENAME(SC.name)
WHEN 'MESSAGE_TYPE' THEN 'MESSAGE TYPE::'+QUOTENAME(SMT.name)
WHEN 'REMOTE_SERVICE_BINDING' THEN 'REMOTE SERVICE BINDING::'+QUOTENAME(RSB.name)
WHEN 'ROUTE' THEN 'ROUTE::'+QUOTENAME(R.name)
WHEN 'SERVICE' THEN 'SERVICE::'+QUOTENAME(SBS.name)
WHEN 'FULLTEXT_CATALOG' THEN 'FULLTEXT CATALOG::'+QUOTENAME(FC.name)
WHEN 'FULLTEXT_STOPLIST' THEN 'FULLTEXT STOPLIST::'+QUOTENAME(FS.name)
WHEN 'SEARCH_PROPERTY_LIST' THEN 'SEARCH PROPERTY LIST::'+QUOTENAME(RSPL.name)
WHEN 'SYMMETRIC_KEYS' THEN 'SYMMETRIC KEY::'+QUOTENAME(SK.name)
WHEN 'CERTIFICATE' THEN 'CERTIFICATE::'+QUOTENAME(CER.name)
WHEN 'ASYMMETRIC_KEY' THEN 'ASYMMETRIC KEY::'+QUOTENAME(AK.name)
END COLLATE Latin1_General_100_BIN AS securable,
'TO '+QUOTENAME(DP.name) AS grantee,
CASE WHEN P.state_desc = 'GRANT_WITH_GRANT_OPTION' THEN 'WITH GRANT OPTION' ELSE '' END AS grant_option,
'AS '+QUOTENAME(G.name) AS grantor
FROM sys.database_permissions AS P
LEFT JOIN sys.schemas AS S
ON P.major_id = S.schema_id
LEFT JOIN sys.all_objects AS O
JOIN sys.schemas AS OS
ON O.schema_id = OS.schema_id
ON P.major_id = O.object_id
LEFT JOIN sys.types AS T
JOIN sys.schemas AS TS
ON T.schema_id = TS.schema_id
ON P.major_id = T.user_type_id
LEFT JOIN sys.xml_schema_collections AS XSC
JOIN sys.schemas AS XSS
ON XSC.schema_id = XSS.schema_id
ON P.major_id = XSC.xml_collection_id
LEFT JOIN sys.columns AS C
ON O.object_id = C.object_id
AND P.minor_id = C.column_id
LEFT JOIN sys.database_principals AS PR
ON P.major_id = PR.principal_id
LEFT JOIN sys.assemblies AS A
ON P.major_id = A.assembly_id
LEFT JOIN sys.service_contracts AS SC
ON P.major_id = SC.service_contract_id
LEFT JOIN sys.service_message_types AS SMT
ON P.major_id = SMT.message_type_id
LEFT JOIN sys.remote_service_bindings AS RSB
ON P.major_id = RSB.remote_service_binding_id
LEFT JOIN sys.services AS SBS
ON P.major_id = SBS.service_id
LEFT JOIN sys.routes AS R
ON P.major_id = R.route_id
LEFT JOIN sys.fulltext_catalogs AS FC
ON P.major_id = FC.fulltext_catalog_id
LEFT JOIN sys.fulltext_stoplists AS FS
ON P.major_id = FS.stoplist_id
LEFT JOIN sys.registered_search_property_lists AS RSPL
ON P.major_id = RSPL.property_list_id
LEFT JOIN sys.asymmetric_keys AS AK
ON P.major_id = AK.asymmetric_key_id
LEFT JOIN sys.certificates AS CER
ON P.major_id = CER.certificate_id
LEFT JOIN sys.symmetric_keys AS SK
ON P.major_id = SK.symmetric_key_id
JOIN sys.database_principals AS DP
ON P.grantee_principal_id = DP.principal_id
JOIN sys.database_principals AS G
ON P.grantor_principal_id = G.principal_id
WHERE P.grantee_principal_id NOT IN (USER_ID('public'))
AND P.permission_name NOT in ('CONNECT')

--------------------
--Access user roles

--List all access provisioned to a sql user or windows user/group directly
SELECT
    LoginName = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                END,
    [DatabaseUserName] = princ.[name],    
    [Role] = null,    
    [PermissionType] = perm.[permission_name],    
    [PermissionState] = perm.[state_desc],    
    [ObjectType] = obj.type_desc,--perm.[class_desc],    
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM  
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN      
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U') AND
  perm.[permission_name]!='CONNECT'
 
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    LoginName = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],    
    [PermissionType] = perm.[permission_name],    
    [PermissionState] = perm.[state_desc],    
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM  
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN      
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
  --where roleprinc.[name] not in ('db_owner','db_securityadmin','db_accessadmin','db_backupoperator','db_ddladmin','db_datawriter','db_datareader','db_denydatawriter','db_denydatareader')

UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    LoginName = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',    
    [Role] = roleprinc.[name],    
    [PermissionType] = perm.[permission_name],    
    [PermissionState] = perm.[state_desc],    
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM  
    --Roles
    sys.database_principals roleprinc
LEFT JOIN      
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]                
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
  AND perm.[permission_name]!='CONNECT'
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

--------------------



DB user access and roles in SQL DB

Oops, something's wrong below.