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.

33 comments:

Unknown 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 .

Unknown said...

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

Quality Managment Services | Austere Technologies

Deepika said...

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

Software Security Services | Austere Technologies

Unknown 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

Deepika said...

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

Best Commerce College| Avinash college of commerce

Deepika said...

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

chartered accountant coaching in hyderabad | Avinash college of commerce

Deepika said...

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

Best chartered accountant course in Hyderabad | Avinash college of commerce

sandy said...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book best Andaman honeymoon packages

sandy said...

Hi Thanks for the nice information its very useful to read your blog. We provide best Cost And Management Accountancy (CMA)

Deepika said...

Excellent informative blog, keep for sharing.

Best System Integration services | Massil Technologies

Ishu Sathya said...

Thanks for the lovely blog!!!Keep posting.

Software testing training in chennai
Salesforce Training in Chennai
Big Data Training in Chennai
Hadoop Training in Chennai
Android Training in Chennai
Selenium Training in Chennai
Digital Marketing Training in Chennai
JAVA Training in Chennai
Best JAVA Training institute in Chennai

jefrin said...

Good to read this post
Best software testing training in chennai

Sivanandhana Girish said...

This was an excellent post. This blog was really comprehensive and helpful to me. Thanks for sharing.
English Speaking Classes in Mumbai
English Speaking Course in Mumbai
Best English Speaking Classes in Mumbai
Spoken English Classes in Mumbai
English Classes in Mumbai
Spoken English in Chennai
IELTS Coaching in Chennai
IELTS Coaching Centre in Chennai
IELTS Training in Chennai
IELTS Classes in Mumbai

Sugantha Raja said...

Wonderful blog & good post. Its really helpful for me, awaiting for more new post. Keep Blogging!
Python Training in Chennai | Python Training Institute in Chennai

jefrin said...

And indeed, I’m just always astounded concerning the remarkable things served by you. Some four facts on this page are undeniably the most effective I’ve had.
Data science Course Training in Chennai |Best Data Science Training Institute in Chennai
RPA Course Training in Chennai |Best RPA Training Institute in Chennai
AWS Course Training in Chennai |Best AWS Training Institute in Chennai
Devops Course Training in Chennai |Best Devops Training Institute in Chennai
Selenium Course Training in Chennai |Best Selenium Training Institute in Chennai
Java Course Training in Chennai | Best Java Training Institute in Chennai

Sahil said...

We Provide IELTS Coaching in bangalore

Academy of IELTS said...


Join #1 IELTS Coaching Ahmedabad at Navin Julka's Academy & Get band 8 and Above. Rated as the best IELTS Coaching Centre in Naranpura, Ahmedabad. Navin Julka's Academy provides training & numerous practice tests in listening, writing, speaking, and reading to get your dream IELTS Score! Call 081288 47113 for more details.

Tamil novels said...

Thanks for sharing this valuable post. It was very useful to us.
Tamil novel writers
Ramanichandran novels PDF
srikala novels PDF
Mallika manivannan novels PDF
muthulakshmi raghavan novels PDF
Infaa Alocious Novels PDF
N Seethalakshmi Novels PDF
Sashi Murali Tamil Novels PDF Download

Unknown said...

This is the perfect webpage for everyone who hopes to understand this topic. You know a whole lot its almost tough to argue with you (not that I personally would want to…HaHa). You definitely put a brand new spin on a topic that's been written about for ages. Excellent stuff, just wonderful! Webinfotechsolution

ISWARYASEO said...

Conversational AI is AI-driven virtual assistant that interacts with the user to provide seamless and automated support with minimal human intervention. They use the technology of Artificial Intelligence to understand the context of the questions asked and provide personalized support that is unique for the concerned user.

Conversational AI service desk

IT courses in Chandigarh said...

Nice information, Thanks for sharing this.
Java training in Chandigarh
Android training in Chandigarh
Tally training in Chandigarh

TRONIX said...

Interesting article, thanks for sharing it with us

Python training institution in Hyderabad

Futurefinders said...

Thanks for sharing information Nice information, Thanks for sharing this.
Best python training in Chandigarh
Best java training in Chandigarh
Best PHP training in Chandigarh

saikumar0801 said...

Thank you for sharing this Beautiful Blog.....

Python Training in Hyderabad

BonntechSolutions said...

Very good Blog, Really Helpful
Digital Marketing Services in Chandigarh
Game Development in Chandigarh
Web Development in Chandigarh

Magikal Elephant said...

nice
diploma in hotel and tourism in Chandigarh
diploma in management in Chandigarh
diploma in computers in Chandigarh

Speaking in public said...

Helpful blog with helpful information, thanks for sharing this.
Python training in Chandigarh
JAVA training in Chandigarh
Digital Marketing training in Chandigarh
Networking training in Chandigarh

bonntechsolutions said...

Wonderful blog . Its really helpful.
Python training in Chandigarh
Game development training in Chandigarh
Web development training in Chandigarh
Moblie App development training in Chandigarh

Anonymous said...

very good blog really helpful
java training in Mohali
full stack training in Mohali
digital marketing training in Mohali python training in Mohali

bonntech said...

very nice blog thanks for sharing information
python training in Mohali
java training in Mohali
networking training in Mohali
android training in Mohali

Muskan said...

Nice Blog. well explained. Also check- Ethical Hacking Training in India Indian Cyber Security Solutions

Muskan said...

I have been following this website for so long. VERY INFORMATIVE BLOG. KEEP SHARING SUCH A GOOD ARTICLES. Also Check, Hope it helps- Data Science Course Certification Digital Marketing Course Certification Unified Mentor- Best Online Learning Platform

bonntech said...

I get a good experiences in bonntech solution the management is good Bonntech solution provide placement.

android in Mohali
ios training in Mohali
.net training in Mohali

Post a Comment

Ads