:::: MENU ::::

Generate object level permissions in a database

  • Nov 12 / 2014
  • 0
dbDigger, Monitoring and Analysis, Security and Permissions

Generate object level permissions in a database

Following script can be used to generate object level permissions in a database. We can filter the results for specific user or type.

 SELECT Us.name AS username,  
     us.type_desc AS UserType,  
     schema_name(obj.schema_id)+'.'+Obj.name AS objectName,  
     dp.permission_name AS permission ,  
     dp.state_desc AS PermissionStatus  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals Us ON dp.grantee_principal_id = Us.principal_id  
 JOIN sys.objects Obj ON dp.major_id = Obj.object_id --where us.type_desc <> 'DATABASE_ROLE'  
 ORDER BY Us.name  
 -- get user permissions on whole DB  
 EXECUTE AS USER = 'userName';  
 SELECT * FROM fn_my_permissions(NULL, 'DATABASE')   
 -- get user permissions on whole DB  
 select  
  a.class_desc, permission_name, state_desc, b.name  
 from sys.database_permissions a  
 inner join sys.database_principals b  
  on a.grantee_principal_id = b.principal_id  
  and permission_name not in ('connect','view definition','SELECT')  
  and b.type_desc in ('SQL_USER','WINDOWS_USER')  
  and state_desc = 'GRANT'  
  and class_desc = 'DATABASE'  

Consult us to explore the Databases. Contact us