Re: Roles and permission mapping table

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" <munish.narula@xxxxxxxxx> wrote in message
news:8C928ADD-7A67-4432-91B1-8635B7027322@xxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Roles and permission mapping table
    ... 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_Namefunction ...
    (microsoft.public.sqlserver.security)
  • SQL Server role permissions
    ... How can I retrieve a list of objects and permissions for a specified role? ... I would like to list all stored procedures which a role has ...
    (microsoft.public.sqlserver.security)
  • Re: DB Access for SQL server
    ... PRINT 'You may not GRANT INSERT permissions on authors.' ... How can I retrieve a list of objects and permissions for a specified role? ...
    (microsoft.public.sqlserver.security)
  • ADP, Application Role, and objects
    ... The above link is to an atricle on how to implement SQL Server Application ... After you connect with your ADP, fire a bit of code to set the ... third party tools to view the data on the same database. ... Scenario 1 - If I explicitly grant permissions on that object to the user ...
    (microsoft.public.access.adp.sqlserver)
  • Re: field level security question
    ... Vyas, MVP ... listBox control that gets data from a query of the sql server table. ... > recent change in requirement) I see the option to limit permissions right ... > utility opens except their is just no data. ...
    (microsoft.public.sqlserver.security)