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.

43 comments:

Riya arora 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

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 .

lucky said...

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

Deepika said...

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

Best Software Company | Austere Technologies

Unknown said...

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

Quality Managment Services | Austere Technologies

Deepika said...

VERY INFORMATIVE BLOG. KEEP SHARING SUCH A GOOD ARTICLES.

Mobility 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

Deepika said...

This is a really great informative blog. Keep Sharing.

Best Degree Colleges Hyderabad | Avinash College of Commerce

Deepika said...

Very good informative blog, keep sharing.

Best junior college 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...

Thank you for sharing this valuable information. But get out this busy life and find some peace with a beautiful trip. book ANDAMAN TOUR PACKAGE@14599/

Deepika said...

Good information.

B.com Integrated Courses | Avinash College of commerce

Deepika said...

Best informative blog. Thank you.

cima courses in hyderabad | ISFS

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

sandy said...

Hi Thanks for the nice information its very useful to read your blog. We provide best company secretary course

Deepika said...

Your article gives lots of information to me. Thanks for sharing.
acca course in hyderabad | ISFS

Deepika said...

Thanks for sharing a valuable information to us. Thanks for your blog.
CIMA training institute in Hyderabad | ISFS

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

travelkida said...

Hello, This post is really nice and informative. The explanation given is really comprehensive and informative. I am feeling happy to comment on this post. good job keep it up. andaman trip andaman tour package

online idea lab said...

Thank you for excellent article.You made an article that is interesting.
Best AWS certification training courses. Build your AWS cloud skills with expert instructor- led classes. Live projects, Hands-on training,24/7 support.
https://onlineidealab.com/aws-certification/

Patrick Co Eban said...

I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
Cyber Security Projects for Final Year

JavaScript Training in Chennai

Project Centers in Chennai

JavaScript Training in Chennai

akhilpatel said...

Thanks for your post! Through your pen I found the problem up interesting! I believe there are many other people who are interested in them just like me! Thanks your shared!... I hope you will continue to have similar posts to share with everyone! I believe a lot of people will be surprised to read this article! Best DevOps online training in hyderabad

vivekvedha said...

Really nice post. Thank you for sharing amazing information.
Digital Marketing Training Course in Chennai | Digital Marketing Training Course in Anna Nagar | Digital Marketing Training Course in OMR | Digital Marketing Training Course in Porur | Digital Marketing Training Course in Tambaram | Digital Marketing Training Course in Velachery

Diyalabs said...

Thanks for sharing such a helpful, and understandable blog. I really enjoyed reading it.

Robots for kids
Robotic Online Classes
Robotics School Projects
Programming Courses Malaysia
Coding courses
Coding Academy
coding robots for kids
Coding classes for kids
Coding For Kids

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

KOSMIK 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

Post a Comment

Ads