:::: MENU ::::

List permissions for logins in SQL Server database

  • Nov 11 / 2008
  • 0
dbDigger, Logins and Users, Monitoring and Analysis, Security and Permissions

List permissions for logins in SQL Server database

It is very important information that which user has which rights and permissions in your SQL Server databases. By this way you may periodically monitor the permissions and chances for any accidental hidden loop hole will be minimized. Following script will provide you the permissions of logins in a database.

 
USE DBName  
 GO  
 SELECT  
 dp.class_desc, dp.permission_name, dp.state_desc,  
 ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name  
 FROM sys.database_permissions dp  
 JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id  
 JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id  
 GO  
Consult us to explore the Databases. Contact us