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?