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

To view all grants on a specific database Select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES

To just view delete grants on a specific database Select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE PRIVILEGE_TYPE = 'DELETE'

Answer 2

To see the grants on an entire DB, select the DB in question, open a new query window, enter - sp_helprotect, execute query

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')

Answer 4

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.