Granting EXEC to all my user sprocs in one hit
From: Alec MacLean (alec.maclean_at_copeohs.com.NO_SPAM)
Date: 04/26/04
- Next message: Steve Thompson: "Re: Problem changing Service Accounts"
- Previous message: Danny Chan: "Uni Code"
- Next in thread: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 26 Apr 2004 19:06:10 +0100
Hi,
(I'm a web-programmer, having to double as a DBA, so please forgive me if
this is a stupid question!)
I've been trying to determine if it is possible to:
1. Retrieve all my user-created sprocs for a specified dbo (to a temp
table), then
2. Grant the EXEC privilege to these user-sprocs only (excluding the system
sprocs).
OR:
3. Grant the EXEC permission using SEMgr across all my user-sprocs at one go
instead of bashing the daylights out of my spacebar.
I'm trying to achieve this because I have two dbo's: one production and one
dev.
I'm detaching the live copy and copying the files across to dev machine,
then re-attaching them in their respective places, in order to get a
snapshot of the data in the production version across to my dev copy (for
testing report queries and saving me creating a LOAD of fake data).
# because of the relationships and number of tables, exporting data from
master to dev is a PITA, as I'd have to purge all the dev tables and set
identity insert on the export transformation - I suppose I ought to build an
admin-only procedure to do all this...:( #
Once I've re-attached the dev copy, I've found I have to remove the assigned
built-in account from the dbo's user list and re-add the user. This then
means I've got to add the exec permissions as well, which while not a
difficult task in SEMgr, it is a pain to do this way as I currently have
over 200 sprocs.
I see that the GRANT syntax allows me to set the permission, but only for
one sproc at a time. If I can retrieve a list of the "usp_" prefixed sprocs
for my specific dbo, I could put these in a temp table, then loop this and
set the permissions.
Surely this type of info is available in one of the system tables, as this
must be how the SEMgr permissions dialog is populated?
TIA for any help.
-- Alec MacLean
- Next message: Steve Thompson: "Re: Problem changing Service Accounts"
- Previous message: Danny Chan: "Uni Code"
- Next in thread: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
