Script to check database user permissions

On Sunday I was responding to a question in one of the forums where the jobs were failing with permission denied error. During the troubleshooting I wanted to check if the permission for those stored procedures were explicitly denied for the SQL Server Agent/Job owner account.

On SQL Server 2005 and above, I usually use the below script to check the permissions granted/denied for database users.

SELECT
USER_NAME(dppriper.grantee_principal_id) AS [UserName],
dppri.type_desc AS principal_type_desc,
dppriper.class_desc,
OBJECT_NAME(dppriper.major_id) AS object_name,
dppriper.permission_name,
dppriper.state_desc AS permission_state_desc
FROM    sys.database_permissions dppriper
INNER JOIN sys.database_principals dppri
ON dppriper.grantee_principal_id = dppri.principal_id

The output of this script would be like the one in this example.

But on that instance, the permission was not denied for any of the stored procedures. Still trying to figure out the possibilities.

4 thoughts on “Script to check database user permissions

  1. Ken Trock

    This is a useful script that I run in my environment from time to time. I added a tweak to show if a login is disabled.

    SELECT
    USER_NAME(dppriper.grantee_principal_id) AS [UserName],
    sppri.is_disabled,
    dppri.type_desc AS principal_type_desc,
    dppriper.class_desc,
    OBJECT_NAME(dppriper.major_id) AS object_name,
    dppriper.permission_name,
    dppriper.state_desc AS permission_state_desc
    FROM sys.database_permissions dppriper
    INNER JOIN sys.database_principals dppri
    ON dppriper.grantee_principal_id = dppri.principal_id

    –Added to differentiate disabled logins
    LEFT JOIN sys.server_principals sppri
    ON sppri.sid = dppri.sid
    order by USER_NAME(dppriper.grantee_principal_id) desc

    Reply
  2. Pingback: Unable to get data from Sql Server 2005 (connection time out exception) | PHP Developer Resource

  3. Howard Rothenburg

    –User Permissions

    SELECT [UserName] = ulogin.[name],
    [UserType] = CASE princ.[type]
    WHEN ‘S’ THEN ‘SQL User’
    WHEN ‘U’ THEN ‘Windows User’
    WHEN ‘G’ THEN ‘Windows Group’
    END,
    [DatabaseUserName] = princ.[name],
    [Role] = NULL,
    [PermissionState] = perm.[state_desc],
    [PermissionType] = perm.[permission_name],
    [ObjectType] = CASE perm.[class]
    WHEN 1 THEN obj.type_desc — Schema-contained objects
    ELSE perm.[class_desc] — Higher-level objects
    END,
    [ObjectName] = CASE perm.[class]
    WHEN 1 THEN OBJECT_NAME(perm.major_id) — General objects
    WHEN 3 THEN schem.[name] — Schemas
    WHEN 4 THEN imp.[name] — Impersonations
    END,
    [ColumnName] = col.[name]
    FROM –database user
    sys.database_principals princ
    LEFT JOIN –Login accounts
    sys.server_principals 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]
    LEFT JOIN sys.schemas schem
    ON schem.[schema_id] = perm.[major_id]
    LEFT JOIN sys.database_principals imp
    ON imp.[principal_id] = perm.[major_id]
    WHERE princ.[type] IN (‘S’, ‘U’, ‘G’)
    AND — No need for these system accounts
    princ.[name] NOT IN (‘sys’, ‘INFORMATION_SCHEMA’)
    ORDER BY
    ulogin.[name],
    [UserType],
    [DatabaseUserName],
    [Role],
    [PermissionState],
    [PermissionType],
    [ObjectType],
    [ObjectName],
    [ColumnName]

    Reply
  4. Pingback: SQL Server – List Permissions for User | Daniel Adeniji's - Learning in the Open

Leave a Reply