Re: Granting EXEC to all my user sprocs in one hit
From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 04/26/04
- Next message: CPNZ: "RE: Connecting to SQL across Non-Trusted Domains"
- Previous message: Steve Thompson: "Re: Problem changing Service Accounts"
- In reply to: Alec MacLean: "Granting EXEC to all my user sprocs in one hit"
- Next in thread: Alec MacLean: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: Alec MacLean: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Mon, 26 Apr 2004 21:50:27 +0100
You can run the procedure below (after you have created it in master) in the
context of a user database e.g.
use pubs
go
exec sp_grantexec 'foo',1
-- create the procedure in master
use master
go
create procedure sp_grantexec(@user sysname,@debug int = 0)
as
set nocount on
declare @ret int
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME(@user)
set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM
INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') =
0'
if @debug = 1 print @sql
exec @ret = master.dbo.xp_execresultset @sql,@db
If @ret <> 0
begin
raiserror('Error executing command %s',16,1,@sql)
return -1
end
-- HTH Jasper Smith (SQL Server MVP) I support PASS - the definitive, global community for SQL Server professionals - http://www.sqlpass.org "Alec MacLean" <alec.maclean@copeohs.com.NO_SPAM> wrote in message news:OuU1Sj7KEHA.3332@TK2MSFTNGP10.phx.gbl... > 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: CPNZ: "RE: Connecting to SQL across Non-Trusted Domains"
- Previous message: Steve Thompson: "Re: Problem changing Service Accounts"
- In reply to: Alec MacLean: "Granting EXEC to all my user sprocs in one hit"
- Next in thread: Alec MacLean: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: Alec MacLean: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
