Roles and permission mapping table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Munish Narula - 20 Nov 2008 08:09 GMT

I need to know all the permissions available in MS SQL 2005 and also the
privilleges that each role has been given.

Is there any system table in SQL 2005 from where i can get both these
information.

Thanks in advance.

Reply to this Message

Uri Dimant - 20 Nov 2008 08:44 GMT

Start with
------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-p
z_6f78.asp>
How can I retrieve a list of objects and permissions for a specified role?
------------------------------------------------------------------------------
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).

> For example, I would like to list all stored procedures which a role has
> execute permission for.

This is an example of usage:

SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o

>I need to know all the permissions available in MS SQL 2005 and also the
> privilleges that each role has been given.
[quoted text clipped - 3 lines]
>
> Thanks in advance.

Reply to this Message

Munish Narula - 20 Nov 2008 10:02 GMT

Thanks for the reply.

But can i get a query/script for getting this data.

> Start with
> ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-p
z_6f78.asp>
[quoted text clipped - 22 lines]
> >
> > Thanks in advance.

Reply to this Message

Uri Dimant - 20 Nov 2008 10:22 GMT

Hi
select [Role] = r.name, [Member] = m.name
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

> Thanks for the reply.
>
[quoted text clipped - 28 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Munish Narula - 20 Nov 2008 10:47 GMT

hi,
Say i have a role called db_owner.

If i have to list down all the privilleges for this role, which table should
i refer to.

Is there any query/script for it.

> Hi
> select [Role] = r.name, [Member] = m.name
[quoted text clipped - 36 lines]
> >> >
> >> > Thanks in advance.

Reply to this Message

Uri Dimant - 20 Nov 2008 12:33 GMT

Start reading here
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/42ceee35-b866-42d5-b633-d2930da2a9bf.htm

> hi,
> Say i have a role called db_owner.
[quoted text clipped - 48 lines]
>> >> >
>> >> > Thanks in advance.

Reply to this Message

Munish Narula - 21 Nov 2008 03:36 GMT

Is this a web link.

It does not take me anywhere!

> Start reading here
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/42ceee35-b866-42d5-b633-d2930da2a9bf.htm
[quoted text clipped - 51 lines]
> >> >> >
> >> >> > Thanks in advance.

Reply to this Message

Uri Dimant - 23 Nov 2008 07:35 GMT

Hi
This is from within BOL

> Is this a web link.
>
[quoted text clipped - 58 lines]
>> >> >> >
>> >> >> > Thanks in advance.

Reply to this Message