Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How can I list ALL permissions granted to a custom database role?

Avatar of LarryHennig
LarryHennigFlag for Canada asked on
Microsoft SQL Server 2005
11 Comments1 Solution779 ViewsLast Modified:
How can I use TSQL to view ALL permissions that are granted to a role, especially permissions that do not show up in the GUI, such as View Definition for the entire database?

Background:

Using TSQL, I created a custom role in MSDB and granted "view definition" to that role.  When I view the "securables" properties for that role in th SSMS GUI, the list is empty.   If I add the MSDB database to the securiables list, the View Definition permission is selected (as expected), but the next time I open the properties of the role, the securables list is again empty.  

Ok, so it seems there is a minor bug in the GUI that prevents it from displaying the database if "View Definition" is the only right granted.  I am worried that other permissions might not appear in the list.  I need to see ALL of the permissions that were granted to that role.

How can I use TSQL to view all permissions that are granted to a role, especially permissions that do not show up in the GUI, such as View Definition for the entire database?

I am running SQL Server 2005 SP3.
ASKER CERTIFIED SOLUTION
Avatar of G Godwin
G GodwinFlag of United States of America imageDatabase Administrator

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answers