(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 MacLeanYou 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
>
>|||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...
if[vbcol=seagreen]
> system
one[vbcol=seagreen]
> go
> one
(for[vbcol=seagreen]
build[vbcol=seagreen]
> an
> assigned
then[vbcol=seagreen]
for[vbcol=seagreen]
> sprocs
and[vbcol=seagreen]
this[vbcol=seagreen]
>|||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...
> the
+[vbcol=seagreen]
=[vbcol=seagreen]
> if
> one
and[vbcol=seagreen]
machine,[vbcol=seagreen]
> (for
from[vbcol=seagreen]
set[vbcol=seagreen]
> build
> then
have[vbcol=seagreen]
> for
> and
> this
>|||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...
> within
in[vbcol=seagreen]
''.''[vbcol=seagreen]
> +
OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'')[vbcol=seagreen]
> =
me[vbcol=seagreen]
temp[vbcol=seagreen]
at[vbcol=seagreen]
> and
> machine,
> from
> set
a[vbcol=seagreen]
> have
only[vbcol=seagreen]
prefixed[vbcol=seagreen]
this[vbcol=seagreen]
as[vbcol=seagreen]
>|||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...
>in
>''.''
> OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'
'IsMSShipped'')
>me
>temp
>at
>a
>only
>prefixed
>this
>as
>|||Thanks to all for the warning info.
Alec MacLean
>"joe"
> "Sue Hoegemeier"
>"Jasper Smith"
