Sunday, March 25, 2012

how to capture object permissions in the database in sql server

Here is the correct one:

select * from sys.database_permissions would give the correct permissions for all the users and all the objects.
Here is a sample query to list all the permissions;
i got this from the following url:
http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx


select USER_NAME(p.grantee_principal_id) AS principal_name,
        dp.type_desc AS principal_type_desc,
        p.class_desc,
        OBJECT_NAME(p.major_id) AS object_name,
        p.permission_name,
        p.state_desc AS permission_state_desc
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id


The below one isn't the correct one, as per MSDN:
 http://msdn.microsoft.com/en-us/library/ms186233.aspx

"Returns one row for each table privilege that is granted to or granted by the current user in the current database"

use dbname
go
select * from information_schema.TABLE_PRIVILEGES

GRANTOR    GRANTEE    TABLE_CATALOG    TABLE_SCHEMA    TABLE_NAME    PRIVILEGE_TYPE    IS_GRANTABLE
dbo          user1         db1                  schema1        table1             DELETE        NO
dbo          user2         db2                  schema2        table2             INSERT         NO

No comments:

Post a Comment