Any way to query EXECUTE perms on stored procs?

I've recently been tasked with duplicating the permissions from one
account to another. We have a development, system test, and production
SQL Server, and approximately 35 databases in each. We use a fine
level of control on this particular account because it's what the
applications use to log in.
I've granted datareader/datawriter to the new account for all the dbs.
Each db, however, has a ton of stored procedures. Not all of them are
executable by the original account. I found a script that grants
access to all user stored procs, which is ok and will save me a few
days worth of point-and-click - but I'd be a happier DBA if I could
find a way to query the execute permissions on the stored procs to copy
them to the new account.
Any ideas? Below is the script I've used so far on the dev server.
SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin
with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs
Thanks,
Bill
A.K.A. PSPDBAThe PERMISSIONS system function can help you identify permission for the
current user:
http://msdn.microsoft.com/library/d...br />
6f78.asp
You could use it in combination with the SETUSER function:
http://msdn.microsoft.com/library/d...br />
6f78.asp
ML
http://milambda.blogspot.com/|||Ok, the sp_helprotect gives me back a list of the execute permissions
for the first user. I don't see how SETUSER is going to help me..|||PSPDBA (williambr@.state.pa.us) writes:
> Ok, the sp_helprotect gives me back a list of the execute permissions
> for the first user. I don't see how SETUSER is going to help me..
I guess the idea is:
SETUSER 'accountunderinvestigation'
go
SELECT name
FROM sysobjects
WHERE xtype = 'P'
AND permissions(id) & 32 = 1
go
SETUSER
That would list all the procedures that the account has permissions to
execute.
I should not that this solution contains three elements that are
deprecated in SQL 2005:
* The SETUSER command (Use EXECUTE AS instead)
* sysobjects (use sys.procedures instead)
* permissions(). (Use fn_my_permissions instead).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx