Query execute permissions on stored procs
- From: williambr@xxxxxxxxxxx
- Date: 21 Apr 2006 05:49:23 -0700
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
.
- Prev by Date: How to allow normal user (not Administrator) deployment of SSAS Cube?
- Next by Date: Re: public role question
- Previous by thread: How to allow normal user (not Administrator) deployment of SSAS Cube?
- Next by thread: Re: public role question
- Index(es):
Relevant Pages
|
|
