Re: Granting EXEC to all my user sprocs in one hit
From: joe (pearl_77_at_hotmail.com)
Date: 04/28/04
- Next message: Dan Guzman: "Re: Execute SQL commands in context of another active connection or howto IS_MEMBER for another connected user?"
- Previous message: JL Fleming: "Revoking Permissions"
- In reply to: Sue Hoegemeier: "Re: 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: Wed, 28 Apr 2004 17:24:34 -0400
okay, fine. I think sqlstring still can not exceed 4000 characters,
otherwise it will fail.
"Sue Hoegemeier" <Sue_H@nomail.please> wrote in message
news:etlv80l5p44jo850p210akks23360dq8dl@4ax.com...
> 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: Dan Guzman: "Re: Execute SQL commands in context of another active connection or howto IS_MEMBER for another connected user?"
- Previous message: JL Fleming: "Revoking Permissions"
- In reply to: Sue Hoegemeier: "Re: 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
|
|
