 |
|
|
|
Next: Help creating schedule
|
| Author |
Message |
External
Since: Dec 24, 2007
Posts: 13
|
(Msg. 1) Posted: Thu Nov 20, 2008 12:09 am
Post subject: Roles and permission mapping table
Archived from groups: microsoft>public>sqlserver>security (more info?)
|
|
|
|
|
| Back to top |
|
 | |
External
Since: Aug 24, 2003
Posts: 739
|
(Msg. 2) Posted: Thu Nov 20, 2008 4:25 am
Post subject: Re: Roles and permission mapping table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Start with
------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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
"Munish Narula" wrote in message
>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. |
|
| Back to top |
|
| |
External
Since: Dec 24, 2007
Posts: 13
|
(Msg. 3) Posted: Thu Nov 20, 2008 4:25 am
Post subject: Re: Roles and permission mapping table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Thanks for the reply.
But can i get a query/script for getting this data.
"Uri Dimant" wrote:
> Start with
> ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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
>
>
> "Munish Narula" wrote in message
>
> >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.
>
>
> >> Stay informed about: Roles and permission mapping table |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 739
|
(Msg. 4) Posted: Thu Nov 20, 2008 6:25 am
Post subject: Re: Roles and permission mapping table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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
"Munish Narula" wrote in message
> Thanks for the reply.
>
> But can i get a query/script for getting this data.
>
>
> "Uri Dimant" wrote:
>
>> Start with
>> ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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
>>
>>
>> "Munish Narula" wrote in message
>>
>> >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.
>>
>>
>> >> Stay informed about: Roles and permission mapping table |
|
| Back to top |
|
| |
External
Since: Dec 24, 2007
Posts: 13
|
(Msg. 5) Posted: Thu Nov 20, 2008 6:25 am
Post subject: Re: Roles and permission mapping table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
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.
"Uri Dimant" wrote:
> 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
>
>
>
> "Munish Narula" wrote in message
>
> > Thanks for the reply.
> >
> > But can i get a query/script for getting this data.
> >
> >
> > "Uri Dimant" wrote:
> >
> >> Start with
> >> ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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
> >>
> >>
> >> "Munish Narula" wrote in message
> >>
> >> >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.
> >>
> >>
> >>
>
>
> >> Stay informed about: Roles and permission mapping table |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 739
|
(Msg. 6) Posted: Thu Nov 20, 2008 8:25 am
Post subject: Re: Roles and permission mapping table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Start reading here
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/42ceee35-b866-42d5-b633-d2930da2a9bf.htm
"Munish Narula" wrote in message
> 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.
>
>
>
>
>
> "Uri Dimant" wrote:
>
>> 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
>>
>>
>>
>> "Munish Narula" wrote in message
>>
>> > Thanks for the reply.
>> >
>> > But can i get a query/script for getting this data.
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Start with
>> >> ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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
>> >>
>> >>
>> >> "Munish Narula" wrote in message
>> >>
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>> >> Stay informed about: Roles and permission mapping table |
|
| Back to top |
|
| |
External
Since: Dec 24, 2007
Posts: 13
|
(Msg. 7) Posted: Thu Nov 20, 2008 7:36 pm
Post subject: Re: Roles and permission mapping table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Is this a web link.
It does not take me anywhere!
"Uri Dimant" wrote:
> Start reading here
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/42ceee35-b866-42d5-b633-d2930da2a9bf.htm
>
>
>
>
> "Munish Narula" wrote in message
>
> > 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.
> >
> >
> >
> >
> >
> > "Uri Dimant" wrote:
> >
> >> 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
> >>
> >>
> >>
> >> "Munish Narula" wrote in message
> >>
> >> > Thanks for the reply.
> >> >
> >> > But can i get a query/script for getting this data.
> >> >
> >> >
> >> > "Uri Dimant" wrote:
> >> >
> >> >> Start with
> >> >> ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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
> >> >>
> >> >>
> >> >> "Munish Narula" wrote in message
> >> >>
> >> >> >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.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
> >> Stay informed about: Roles and permission mapping table |
|
| Back to top |
|
| |
External
Since: Aug 24, 2003
Posts: 739
|
(Msg. 8) Posted: Sun Nov 23, 2008 3:25 am
Post subject: Re: Roles and permission mapping table [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
Hi
This is from within BOL
"Munish Narula" wrote in message
> Is this a web link.
>
> It does not take me anywhere!
>
>
> "Uri Dimant" wrote:
>
>> Start reading here
>> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/42ceee35-b866-42d5-b633-d2930da2a9bf.htm
>>
>>
>>
>>
>> "Munish Narula" wrote in message
>>
>> > 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.
>> >
>> >
>> >
>> >
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>> >> "Munish Narula" wrote in message
>> >>
>> >> > Thanks for the reply.
>> >> >
>> >> > But can i get a query/script for getting this data.
>> >> >
>> >> >
>> >> > "Uri Dimant" wrote:
>> >> >
>> >> >> Start with
>> >> >> ------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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
>> >> >>
>> >> >>
>> >> >> "Munish Narula" wrote in message
>> >> >>
>> >> >> >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.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> >> Stay informed about: Roles and permission mapping table |
|
| Back to top |
|
| |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
