Skip to content

Managing SQL Server Permissions

    What are GRANT, DENY, and REVOKE Permissions

    GRANT – this allows the principal to perform an action

    DENY – this Denies permission to a login (principal)

    REVOKE – These remove the grant or deny permission to the securable

    A deny will override a grant. This means that if a user is denied permission they cannot inherit a grant from another source

    Viewing the Permissions Script

    SELECT  
        [UserName] = 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')
    UNION
    --List all access provisioned to a sql user or windows user/group through a database or application role
    SELECT  
        [UserName] = 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]
    UNION
    --List all access provisioned to the public role, which everyone gets by default
    SELECT  
        [UserName] = '{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
    ORDER BY
        princ.[Name],
        OBJECT_NAME(perm.major_id),
        col.[name],
        perm.[permission_name],
        perm.[state_desc],
        obj.type_desc--perm.[class_desc] 
    

    Also See: