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.  PSPDBA

The PERMISSIONS system function can help you identify permission for the
current user: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_6f78.asp

You could use it in combination with the SETUSER function: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_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 (willia***@state.pa.us) writes:
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).

Why does (SqlInt32)sqlCommand.ExecuteScalar(); : Throw Specified cast is not valid.
Data Parse Challenge
function versus stored proc
Import DBF File into SQL Server using vb.net
1434 problem
pulling unique records from this query
Multiple queries SQL
OPENXML Insert into table failure not producing @@Error
How to insert empty string
How to use EXEC