Re: Granting EXEC to all my user sprocs in one hit
From: Jasper Smith (jasper_smith9_at_hotmail.com)
Date: 04/27/04
- Next message: mary: "sql server upgrade from 7 to 2000"
- Previous message: Sue Hoegemeier: "Re: how can a user who is in a role assign his role to another user?"
- In reply to: Alec MacLean: "Re: Granting EXEC to all my user sprocs in one hit"
- Next in thread: joe: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: joe: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Tue, 27 Apr 2004 13:12:19 +0100
You got it :-)
xp_execresultset runs the dynamic sql to generate the commands and then
executes them
-- 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:OXgFCuELEHA.1348@TK2MSFTNGP12.phx.gbl... > Thanks Jasper - looks like exactly what I was looking for. > > If I am interpreting correctly, this assigns the passed user id exec > permission on any sproc that didn't ship with the SQL Server install, within > the context of the database (pubs in your example) it is called from? > > Regards > > -- > Alec MacLean > > > "Jasper Smith" <jasper_smith9@hotmail.com> wrote in message > news:eB1qVA9KEHA.2556@TK2MSFTNGP11.phx.gbl... > > 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: mary: "sql server upgrade from 7 to 2000"
- Previous message: Sue Hoegemeier: "Re: how can a user who is in a role assign his role to another user?"
- In reply to: Alec MacLean: "Re: Granting EXEC to all my user sprocs in one hit"
- Next in thread: joe: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: joe: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
