T-SQL to List all Permissions for a Given Role | SansSQL

Thursday, April 13, 2017

T-SQL to List all Permissions for a Given Role

This script lists all the permissions for a given role.

SELECT DB_NAME() AS DatabaseName
      ,DatabasePrincipals.name AS PrincipalName
      ,DatabasePrincipals.type_desc AS PrincipalType
      ,DatabasePrincipals2.name AS GrantedBy
      ,DatabasePermissions.permission_name AS Permission
      ,DatabasePermissions.state_desc AS StateDescription
      ,SCHEMA_NAME(SO.schema_id) AS SchemaName
      ,SO.Name AS ObjectName
      ,SO.type_desc AS ObjectType
  FROM sys.database_permissions DatabasePermissions LEFT JOIN sys.objects SO
    ON DatabasePermissions.major_id = so.object_id LEFT JOIN sys.database_principals DatabasePrincipals
    ON DatabasePermissions.grantee_principal_id = DatabasePrincipals.principal_id LEFT JOIN sys.database_principals DatabasePrincipals2
    ON DatabasePermissions.grantor_principal_id = DatabasePrincipals2.principal_id
WHERE DatabasePrincipals.name = 'Test' -- Change the Role Name

You can download the script from here.

12 comments:

Riya R said...

informative blog thanks for providing such a great information.
SEO jobs in Hyderabad
SharePoint jobs in Hyderabad
SharePoint jobs in Chennai

Karthika Shree said...

It's interesting that many of the bloggers to helped clarify a few things for me as well as giving.Most of ideas can be nice content.The people to give them a good shake to get your point and across the command.
Java Training in Chennai

Divya madhuri said...

Nice blog with having good information. It’s very useful for everyone. Thanks and keep posting this type of blog CCNA jobs in Hyderabad .

jonatha jose said...

Best Shower Curtains Collection from everywher

lucky said...

LG G7: Here's everything you want to know about the LG G7. Check out LG G7 Price

sam said...

wow...nice blog, very helpful information. Thanks for sharing.

Internet Of Things Services | Austere Technologies

Avi said...

Great article, really very helpful content you made. Thank you, keep sharing.

Digital Transformation Services | Austere Technologies

dev said...

Very good informative article. Thanks for sharing such nice article, keep on up dating such good articles.

Best Software Company | Austere Technologies

ram chinna said...

Nice blog with excellent information. Thank you, keep sharing.

Quality Managment Services | Austere Technologies

divya said...

VERY INFORMATIVE BLOG. KEEP SHARING SUCH A GOOD ARTICLES.

Mobility Services | Austere Technologies

ram said...

Great article, really very helpful content you made. Thank you, keep sharing.

Software Security Services | Austere Technologies

preethi Shetty said...

Needed to compose one little word yet thanks for the suggestions that you are contributed here...
Best Online Software Training Institute | SQL Server Training

Post a Comment