Viewing Role Permissions for Custom Role

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ExcelMan - 28 May 2007 00:04 GMT

I set up a custom role "Finance" and assigned it a combination of some
built-in roles and some specific permissions to specific objects
(securables). However when I go back into the role and look at the
properties of that role, I do not see a list of the specific object
permissions I granted to that role.

Instead I seem to have to go back to those objects, Add them in again,
and then I see that they were already selected.

Is there a place I can see the objects/permission I already granted to
a custom role?

Thanks.

Steven

Reply to this Message

Uri Dimant - 28 May 2007 05:18 GMT

Hi
select r.name as Role, m.name as Member
from sys.database_role_members rm
join sys.database_principals r on rm.role_principal_id = r.principal_id
join sys.database_principals m on
rm.member_principal_id = m.principal_id
order by r.name, m.name

>I set up a custom role "Finance" and assigned it a combination of some
> built-in roles and some specific permissions to specific objects
[quoted text clipped - 11 lines]
>
> Steven

Reply to this Message

Erland Sommarskog - 28 May 2007 23:33 GMT

> I set up a custom role "Finance" and assigned it a combination of some
> built-in roles and some specific permissions to specific objects
[quoted text clipped - 7 lines]
> Is there a place I can see the objects/permission I already granted to
> a custom role?

Which version of SQL 2005 do you have? Specicially which version of
SSMS? I seem to recall that were some really crappy dialogs in this
area, but I just ran a test in SP2: I took a role, went to the Securables
tab, and added some tables and granted rights to some of them. I pressed
OK, and closed the dialog. When I opened the dialog again, and went to
the Securables tab, the objects I had granted rights to appeared.

But if you want to see which role you role is member of, you are out of
luck in that dialog. Use the query that Uri posted.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message