[Webinar] Streamline your web hosting managementRegister Today


Permissions Query problem

Posted on 2008-01-25
Medium Priority
Last Modified: 2012-08-14

I have a fleet of databases each of which have read users who  have execute permissions on a set of stored procedures which all start with rpt_.
I would like to be able to list the permissions on these individual logins, but just for the stored procedures defined.

The following would give me one procedure for the user selected:
EXECUTE AS LOGIN = 'read_user_1';
SELECT * FROM fn_my_permissions ('Rpt_x', 'OBJECT');

The above however does not provide the permission info for all the procedures.

Another issue is that I need this to work on sql server 2000 as well as 2005, as half the servers are still 2000.

Can this be done?

Question by:kinnon_2000
LVL 18

Accepted Solution

brejk earned 800 total points
ID: 20742217
Do you want to display the permissions given to database roles also (users may belong to some of them, roles can imply the effective permissions...)?

Some sample code you may use is in the snippet. The code in the snippet searches for execute permissions given to specific users on stored procedures named with prefix rpt_. It does not search for permissions granted (denied) to (from) roles.
  object_name(p.id) as [object], 
    when p.protecttype in (204,205) then 'GRANT'
    when p.protecttype = 206 then 'DENY'
  end as [grant/deny]
from dbo.sysprotects p with (nolock)
inner join dbo.sysusers u with (nolock)
on p.uid = u.uid
inner join sysobjects o with (nolock)
on p.id = o.id
where o.name like 'rpt\_%' escape '\' 
and u.name in (<put_your_user_names_list_here>) 
and p.action = 224 and o.xtype = 'P'

Open in new window


Author Closing Comment

ID: 31424917
This was great. sorry for the delayed response, I've been away for a short spell.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question