Re: Granting EXEC to all my user sprocs in one hit
From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 04/28/04
- Next message: Neil Pike: "Re: Protecting database from administrators"
- Previous message: Max: "Execute SQL commands in context of another active connection or howto IS_MEMBER for another connected user?"
- In reply to: joe: "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: Wed, 28 Apr 2004 10:14:15 -0600
True but the issue was fixed in a patch over 3 years ago.
-Sue
On Wed, 28 Apr 2004 10:55:51 -0400, "joe"
<pearl_77@hotmail.com> wrote:
>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: Neil Pike: "Re: Protecting database from administrators"
- Previous message: Max: "Execute SQL commands in context of another active connection or howto IS_MEMBER for another connected user?"
- In reply to: joe: "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
|
|
