Re: Granting EXEC to all my user sprocs in one hit
From: joe (pearl_77_at_hotmail.com)
Date: 04/28/04
- Next message: Zach Wells: "Re: A TOUGH ONE - audit Login, Audit Logout connection"
- Previous message: Ilya Margolin: "Re: A TOUGH ONE - audit Login, Audit Logout connection"
- In reply to: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- Next in thread: Sue Hoegemeier: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: Sue Hoegemeier: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 28 Apr 2004 10:55:51 -0400
You should be careful when you use xp_execresultset as it contains buffer
overflow.
The xp_execresultset extended stored procedure does not properly allocate
enough memory when called with a long string as the first parameter...
https://www.appsecinc.com/Policy/PolicyCheck2018.html
"Jasper Smith" <jasper_smith9@hotmail.com> wrote in message
news:OTITtXHLEHA.2704@TK2MSFTNGP10.phx.gbl...
> 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: Zach Wells: "Re: A TOUGH ONE - audit Login, Audit Logout connection"
- Previous message: Ilya Margolin: "Re: A TOUGH ONE - audit Login, Audit Logout connection"
- In reply to: Jasper Smith: "Re: Granting EXEC to all my user sprocs in one hit"
- Next in thread: Sue Hoegemeier: "Re: Granting EXEC to all my user sprocs in one hit"
- Reply: Sue Hoegemeier: "Re: Granting EXEC to all my user sprocs in one hit"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
