How can I view all grants for an SQL Database?

Question

I am using SQL Server 2005, I want to find out what all the grants are on a specific database for all tables. It would also help to find out all tables where the delete grant has been given for a specific user.

Note: this may be similar to this question, but I could not get the selected answer's solution working (if someone could provide a better example of how to use that, it would help as well)

Answer 1

The given solution does not cover where the permission is granted against the schema or the database itself, which do grant permissions against the tables as well. This will give you those situations, too. You can use a WHERE clause against permission_name to restrict to just DELETE.

SELECT 
    class_desc 
  , CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
  , USER_NAME(grantee_principal_id) [User]
  , permission_name
  , state_desc
FROM sys.database_permissions

Also, db_datawriter would need to be checked for membership because it gives implicit INSERT, UPDATE, and DELETE rights, meaning you won't see it show up in the permission DMVs or their derivatives.

Answer 2

I liked the answer from K. Brian Kelly but I wanted a little more information (like the schema) as well as generating the corresponding GRANT and REVOKE statements so I could apply them in different environments (e.g. dev/test/prod).

note you can easily exclude system objects, see commented where clause

select 
    class_desc 
    ,USER_NAME(grantee_principal_id) as user_or_role
    ,CASE WHEN class = 0 THEN DB_NAME()
          WHEN class = 1 THEN ISNULL(SCHEMA_NAME(o.uid)+'.','')+OBJECT_NAME(major_id)
          WHEN class = 3 THEN SCHEMA_NAME(major_id) END [Securable]
    ,permission_name
    ,state_desc
    ,'revoke ' + permission_name + ' on ' +
        isnull(schema_name(o.uid)+'.','')+OBJECT_NAME(major_id)+ ' from [' +
        USER_NAME(grantee_principal_id) + ']' as 'revokeStatement'
    ,'grant ' + permission_name + ' on ' +
        isnull(schema_name(o.uid)+'.','')+OBJECT_NAME(major_id)+ ' to ' +
        '[' + USER_NAME(grantee_principal_id) + ']' as 'grantStatement'
FROM sys.database_permissions dp
LEFT OUTER JOIN sysobjects o
    ON o.id = dp.major_id
-- where major_id >= 1  -- ignore sysobjects

order by 
    class_desc desc
    ,USER_NAME(grantee_principal_id)
    ,CASE WHEN class = 0 THEN DB_NAME()
         WHEN class = 1 THEN isnull(schema_name(o.uid)+'.','')+OBJECT_NAME(major_id)
         WHEN class = 3 THEN SCHEMA_NAME(major_id) end
    ,permission_name

Answer 3

To list all the permissions that can be controlled you can use the function fn_my_permission. This query lists all permissions on server:

select * from fn_my_permissions(NULL, NULL)

You have to login using an account that has sysadmin role.

You can refine the function calls using following parameters.

For all permissions on database:

select * from fn_my_permissions(NULL, 'database')

For all permissions on the dbo schema:

select * from fn_my_permissions('dbo', 'schema')

For all permissions on a table:

select * from fn_my_permissions('dbo.test', 'object')