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.

47 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

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

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 solution 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

Madhura Shete said...

Thanks for sharing useful information. Best Online Platform for Web Development Course in India I hope this will definitely help you for more knowledge.

bonntech said...

very nice blog , thanks for sharing this information. well explained.

best full stack training in Chandigarh

vv software said...

Nice blog......
Thanks for sharing this valuable post.
Keep posting more

Bonntech said...

Nice Blog...Thank You for sharing this useful information. We are giving the best JAVA course in Chandigarh which can be a defining moment for all students
best Java training in Chandigarh

Vibin said...

With Infycle Technologies' extensive Oracle PLSQL training in Chennai, you can unleash the potential of databases. Get practical experience developing high-performing, effective database applications. Learn the craft of data management and query optimisation with our expert-led courses. Come along to improve your programming abilities and start a fruitful Oracle PLSQL journey. To ensure a better IT career, enrol now! Contact us at +91-750263363 or +91-7504633633 for further information.

Bonntech Solutions said...

Helpful information.
Best Java Training in Chandigarh
Best PHP training in Chandigarh
Best Full Stack Training in Mohali
best digital marketing training in Chandigarh
best full stack training in Chandigarh
best PHP training in Chandigarh

Bonntech Solutions said...

The one-and-a-half-month best mean stack training in Chandigarh preparation program is designed to give students an encounter with business and help them start their professional careers. Our group of experienced senior mentors works together with committed insights into LIVE projects from various company areas. At BONNTECH we support you in choosing a career with the right best mean stack training in Chandigarh training path.

BONNTECH SOLUTION 123 said...

BONNTECH,they are improving the skills.We are responsible for delivering innovations.BONNTECH provide students are fully work oriented ,business based prep and they are allowed the students live activity from the business.They are used in Chandigarh.Students are prepared courses BBA,BCA,MCA etc.

Best Digital Marketing Training in Mohali

Best Web Designing Training in Mohali

Best Computer Graphics Training in Mohali

Bonntech Solutions said...

Bonntech Solutions is a provide the best educations carrier opportunities like Web development , Web designing, Digital marketing, Android, Machine learning & AI, Data science ,python, java , and other programming languages and etc.

Best Digital Marketing Training in Mohali


Best python Training in Mohali

Best AI Training in Mohali

BONNTECH SOLUTION said...

Bonntech Solution is a leading education and training company headquartered in Mohali, dedicated to providing high-quality courses and learning solutions. With a commitment to excellence, Bonntech Solution strives to empower individuals with the skills and knowledge necessary to succeed in today's dynamic and competitive professional landscape.
best game development courses in mohali
best web development courses in mohali
best web desining courses in mohali
best digital marketing courses in mohali

bonntechsolutions said...

Bonntech Solution is the company where you get courses within the range.
Best Digital marketing Training in Mohali
Best Web development Training in Mohali

Bonntech Solutions 85 said...

the information of this blog is really useful
Best Web Development Training in Mohali
Best Digitak Marketing Training in Mohali

Bonntech Solutions said...

Impressed by your article. Good knowledge about SQL .
Best Web development course
Marketing Campaign Training
Mobile App Development

Diligent Security said...

Everybody is welcome in the city of Markham! Diligent – Security Company Markham is an obtainable and accessible security provider in the city. Security Company Markham aims hard to develop and maintain an environment where everybody feels secure, safe, and protected.

prabal singh said...

hi

prabal singh said...

Bonntech Solution is the company where you get courses within the range.

Post a Comment

Ads