Re: Granting EXEC to all my user sprocs in one hit
From: Alec MacLean (alec.maclean_at_copeohs.com.NO_SPAM)
Date: 04/27/04
- Next message: samuelzhu: "how can a user who is in a role assign his role to another user?"
- Previous message: Neil Pike: "Re: Protecting database from administrators"
- In reply to: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- 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: Tue, 27 Apr 2004 12:36:12 +0100
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: samuelzhu: "how can a user who is in a role assign his role to another user?"
- Previous message: Neil Pike: "Re: Protecting database from administrators"
- In reply to: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- 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
|
