Granting EXECUTE permissions to all stored procedures

I want to allow my user to have exec permissions on all stored procs in the
database. Is there a quick way to do this? Right now, the only way I know ho
w to do this is to go into the permissions for the user and check the EXEC c
heckbox for each individual
stored proc...Hi
Execute the following in Query Analyzer with text result (Query menu click
result in text) and copy and paste results
to give you the required script.
select 'grant exec on ' + QUOTENAME(name) + ' to [user_name]'
from sysobjects where type = 'P'
and objectproperty(id,'IsMSShipped')=0
Note:
Replace the user_name with actual user name or role name.
Tahnks
Hari
MCDBA
"DBA72" <anonymous@.discussions.microsoft.com> wrote in message
news:77702A18-57F2-4B75-B6AC-B4D769DA8951@.microsoft.com...
> I want to allow my user to have exec permissions on all stored procs in
the database. Is there a quick way to do this? Right now, the only way I
know how to do this is to go into the permissions for the user and check the
EXEC checkbox for each individual stored proc...

Granting EXEC to all my user sprocs in one hit

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 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"

GRANTING a variable

When exec.
GRANT SELECT ON @.variable_name to user_name
The above does not work due to the variable.
Is there no way of dynamically assigning the table-name?
Thks again.You have to create the complete statement as a string ad then execute it
with EXEC or sp_xecutesql. Read an excellent article at
http://www.algonet.se/~sommar/dynamic_sql.html.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:11f301c3fb91$454511d0$a401280a@.phx.gbl...
> When exec.
> GRANT SELECT ON @.variable_name to user_name
> The above does not work due to the variable.
> Is there no way of dynamically assigning the table-name?
> Thks again.|||Hi,
Yes you can do it, Look in to the below code
declare @.sql nvarchar(200)
declare @.tbl_name varchar(30)
set @.tbl_name='sysobjects'
set @.sql = 'grant select on '+ @.tbl_name + ' to public'
exec sp_executesql @.sql
Thanks
Hari
MCDBA
"Wayne" <anonymous@.discussions.microsoft.com> wrote in message
news:11f301c3fb91$454511d0$a401280a@.phx.gbl...
> When exec.
> GRANT SELECT ON @.variable_name to user_name
> The above does not work due to the variable.
> Is there no way of dynamically assigning the table-name?
> Thks again.sql

GRANT Permission to Users

I need to grant EXEC permission to several users on my Procedures. I would
like to to do this in One GRANT statement. this doesn't run. What do I need
to change.
GRANT EXEC ON
--Proc_Clear_data_CAS_ODS
--Proc_Clear_data_CMS_ODS
--Proc_Clear_data_GAS_ODS
--Proc_load_data_ShipperDim
--Proc_load_data_capacityGrpDim
--Proc_load_data_CAS_To_ODS
--Proc_load_data_CMS_To_ODS
--Proc_load_data_ContractDim
--Proc_load_data_CMS_To_ODS
--Proc_load_data_GAS_To_ODS
--Proc_load_data_PayerDim
--Proc_load_data_pointGrpDim
--Proc_load_data_ShipperDim
TO USER 1
USER 2
USER 3
WITH GRANT OPTION
Hi,
You can not give multiple procedure names in a Grant statement. But the
otherway is possible,
Exexute previlage on a single procedure to multiple users.
Grant exec on proc1 to usr1,ur2,usr3
Thanks
Hari
MCDBA
"baaul" wrote:

> I need to grant EXEC permission to several users on my Procedures. I would
> like to to do this in One GRANT statement. this doesn't run. What do I need
> to change.
> GRANT EXEC ON
> --Proc_Clear_data_CAS_ODS
> --Proc_Clear_data_CMS_ODS
> --Proc_Clear_data_GAS_ODS
> --Proc_load_data_ShipperDim
> --Proc_load_data_capacityGrpDim
> --Proc_load_data_CAS_To_ODS
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_ContractDim
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_GAS_To_ODS
> --Proc_load_data_PayerDim
> --Proc_load_data_pointGrpDim
> --Proc_load_data_ShipperDim
> TO USER 1
> USER 2
> USER 3
> WITH GRANT OPTION

GRANT Permission to Users

I need to grant EXEC permission to several users on my Procedures. I would
like to to do this in One GRANT statement. this doesn't run. What do I need
to change.
GRANT EXEC ON
--Proc_Clear_data_CAS_ODS
--Proc_Clear_data_CMS_ODS
--Proc_Clear_data_GAS_ODS
--Proc_load_data_ShipperDim
--Proc_load_data_capacityGrpDim
--Proc_load_data_CAS_To_ODS
--Proc_load_data_CMS_To_ODS
--Proc_load_data_ContractDim
--Proc_load_data_CMS_To_ODS
--Proc_load_data_GAS_To_ODS
--Proc_load_data_PayerDim
--Proc_load_data_pointGrpDim
--Proc_load_data_ShipperDim
TO USER 1
USER 2
USER 3
WITH GRANT OPTIONHi,
You can not give multiple procedure names in a Grant statement. But the
otherway is possible,
Exexute previlage on a single procedure to multiple users.
Grant exec on proc1 to usr1,ur2,usr3
Thanks
Hari
MCDBA
"baaul" wrote:
> I need to grant EXEC permission to several users on my Procedures. I would
> like to to do this in One GRANT statement. this doesn't run. What do I need
> to change.
> GRANT EXEC ON
> --Proc_Clear_data_CAS_ODS
> --Proc_Clear_data_CMS_ODS
> --Proc_Clear_data_GAS_ODS
> --Proc_load_data_ShipperDim
> --Proc_load_data_capacityGrpDim
> --Proc_load_data_CAS_To_ODS
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_ContractDim
> --Proc_load_data_CMS_To_ODS
> --Proc_load_data_GAS_To_ODS
> --Proc_load_data_PayerDim
> --Proc_load_data_pointGrpDim
> --Proc_load_data_ShipperDim
> TO USER 1
> USER 2
> USER 3
> WITH GRANT OPTION

GRANT permission to lots of tables and sp to db user

Is it possible to grant permissions (select, insert, delete, update, exec)
to a db user to all tables and all stored procedures in a specific db in an
easy (lazy!) way?
I mean, except for clicking in all permission checkboxes in Enterprise
Manager or writing a huge sql script like
grant select, insert, delete, update
on mytable1
to myuser
grant select, insert, delete, update
on mytable2
to myuser
...
grant exec
on mySP1
to myuser
grant exec
on mySP2
to myuser
...
?
Is there another way, like
GRANT select, insert, delete, update
on AllMyTables
to myuser
GRANT exec
on AllmySP
to myuser
?You can use a script like to example below to grant mass permissions
according to your requirements.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT ' +
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
N'SELECT, INSERT, UPDATE, DELETE'
WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'SELECT'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
N'EXECUTE'
END +
N' ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[nam
e]) +
N' TO MyRole'
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Siri" <Siri@.discussions.microsoft.com> wrote in message
news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
> Is it possible to grant permissions (select, insert, delete, update, exec)
> to a db user to all tables and all stored procedures in a specific db in
> an
> easy (lazy!) way?
> I mean, except for clicking in all permission checkboxes in Enterprise
> Manager or writing a huge sql script like
> grant select, insert, delete, update
> on mytable1
> to myuser
> grant select, insert, delete, update
> on mytable2
> to myuser
> ...
> grant exec
> on mySP1
> to myuser
> grant exec
> on mySP2
> to myuser
> ...
> ?
> Is there another way, like
> GRANT select, insert, delete, update
> on AllMyTables
> to myuser
> GRANT exec
> on AllmySP
> to myuser
> ?
>|||Thank you very much! This really helped!
Siri
"Dan Guzman" wrote:

> You can use a script like to example below to grant mass permissions
> according to your requirements.
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(500)
> DECLARE @.LastError int
> DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> SELECT
> N'GRANT ' +
> CASE
> WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
> N'SELECT, INSERT, UPDATE, DELETE'
> WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
> N'SELECT'
> WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
> N'EXECUTE'
> END +
> N' ON ' +
> QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].&#
91;name]) +
> N' TO MyRole'
> FROM
> sysobjects ob
> WHERE
> OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @.GrantStatement
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Siri" <Siri@.discussions.microsoft.com> wrote in message
> news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
>
>

grant permission problem on functions that returns table data type

problem on grant permission to user
Was this post helpful ?
I have two kinds of functions in the database that need to grant exec
permission to user appUser.
The first kind of function return ordinary datatype, let's call it
funcReturnDataType here.
The second kind of function return table datatype, let's call it
funcReturnTable
When I issued the folloing command to appUser, no problem.
grant exec on funcReturnDataType to appUser
However when I issued:
grant exec on funcReturnTable to appUser
I got the following error message:
Server: Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege EXECUTE is not compatible with object.
Any suggestions to resolve this problem?
Thank you!
--
The following is an example of a function that returns a table data type:
CREATE FUNCTION funcReturnTable
(
@.i int
)
RETURNS TABLE
AS
RETURN
(
Select Distinct FoodType from Food where region=@.i
)For a table-valued function, specify SELECT instead of EXEC.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"she" <she@.discussions.microsoft.com> wrote in message
news:3C2893C3-7394-40CE-A098-57C06ED96C57@.microsoft.com...
> problem on grant permission to user
> Was this post helpful ?
>
> I have two kinds of functions in the database that need to grant exec
> permission to user appUser.
> The first kind of function return ordinary datatype, let's call it
> funcReturnDataType here.
> The second kind of function return table datatype, let's call it
> funcReturnTable
> When I issued the folloing command to appUser, no problem.
> grant exec on funcReturnDataType to appUser
> However when I issued:
> grant exec on funcReturnTable to appUser
> I got the following error message:
> Server: Msg 4606, Level 16, State 1, Line 1
> Granted or revoked privilege EXECUTE is not compatible with object.
> Any suggestions to resolve this problem?
> Thank you!
> --
> The following is an example of a function that returns a table data type:
> CREATE FUNCTION funcReturnTable
> (
> @.i int
> )
> RETURNS TABLE
> AS
> RETURN
> (
> Select Distinct FoodType from Food where region=@.i
> )
>
>
>sql

grant exec to windows account

When i do
grant exec on sproc1 to 'domain\user'
it returns
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'domain\user'Hi John
Use [ ] instead of quotes:
grant exec on sproc1 to [domain\user]
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"John Doe" <Johndoe@.jd.com> wrote in message
news:Oj4ImmmbIHA.1212@.TK2MSFTNGP05.phx.gbl...
> When i do
> grant exec on sproc1 to 'domain\user'
> it returns
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 'domain\user'
>

Grant EXEC to SPROCS

I want to grant exec permission to a windows account <domain\user> to 150
stored procedures that are prefixed with usp_ . How do I script this so that
i won't have to do them individually?
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually
Try a script like the one below.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_NAME LIKE 'usp[_]'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:FAD323C1-40A9-46C6-B9EC-F02CFDF3E4B7@.microsoft.com...
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually?
sql

Grant EXEC to SPROCS

I want to grant exec permission to a windows account <domain\user> to 150
stored procedures that are prefixed with usp_ . How do I script this so that
i won't have to do them individually?>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually
Try a script like the one below.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_NAME LIKE 'usp[_]'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:FAD323C1-40A9-46C6-B9EC-F02CFDF3E4B7@.microsoft.com...
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually?

Grant Exec to all UDFs and Stored Procedures

Hi All,
I am little at a loss here. I found a procedure that allows me to
pass two parameters: username and dbname and it grants execute to that
user to all stored procedures on theat specific database.
I couldn't find anything that would allow me to grant execute
permissions on all UDFs as well for a specific user.
Is there anything that I can use?
thank you,
T.Same as you do for a stored procedure. you can grant execute permission only
on scalar udfs.
grant execute on <schema.udf_name> to <database_principal>
AMB
"tolcis" wrote:
> Hi All,
> I am little at a loss here. I found a procedure that allows me to
> pass two parameters: username and dbname and it grants execute to that
> user to all stored procedures on theat specific database.
> I couldn't find anything that would allow me to grant execute
> permissions on all UDFs as well for a specific user.
> Is there anything that I can use?
> thank you,
> T.
>

GRANT EXEC Problem

Hi,
SQL Server does not like my stored procedure below. It complains
about the GRANT EXEC line
What am I doing wrong?
JD
----
DECLARE @.PROCNAME varchar(50)
DECLARE cPROCNAME CURSOR FOR
SELECT NAME
FROM SYSOBJECTS
WHERE XTYPE = 'P' AND CATEGORY = 0
ORDER BY NAME
OPEN cPROCNAME
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME /* Prime the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN
GRANT EXEC ON @.PROCNAME TO PUBLIC <--Problem here
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME
END
CLOSE cPROCNAME
DEALLOCATE cPROCNAMEYou cannot do a grant on a variable. You'll have to use dynamic SQL. (BTW,
in SQL 2005, you can do a GRANT EXEC on an entire SCHEMA)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Joe Delphi" <delphi561@.nospam.cox.net> wrote in message
news:JZLff.5127$xu.344@.fed1read01...
Hi,
SQL Server does not like my stored procedure below. It complains
about the GRANT EXEC line
What am I doing wrong?
JD
----
DECLARE @.PROCNAME varchar(50)
DECLARE cPROCNAME CURSOR FOR
SELECT NAME
FROM SYSOBJECTS
WHERE XTYPE = 'P' AND CATEGORY = 0
ORDER BY NAME
OPEN cPROCNAME
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME /* Prime the cursor */
WHILE @.@.FETCH_STATUS = 0
BEGIN
GRANT EXEC ON @.PROCNAME TO PUBLIC <--Problem here
FETCH NEXT FROM cPROCNAME INTO @.PROCNAME
END
CLOSE cPROCNAME
DEALLOCATE cPROCNAME

GRANT command error

Hello,
I am running a very simple command to grant a stored procedure an EXEC
permission to a login that is created for a NT group. The command is as
follows
GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
I am getting the error like "Incorrect syntax near '\' "
Please note that same name exist for login and database user i.e CORP\AppDev.
Any help in this matter would be greatly appreciated.Surround the user in square brackets. [CORP\AppDev]
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"David" wrote:
> Hello,
> I am running a very simple command to grant a stored procedure an EXEC
> permission to a login that is created for a NT group. The command is as
> follows
> GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> I am getting the error like "Incorrect syntax near '\' "
> Please note that same name exist for login and database user i.e CORP\AppDev.
> Any help in this matter would be greatly appreciated.
>|||Thanks ... I guess i am very slow today :)
"AndyP" wrote:
> Surround the user in square brackets. [CORP\AppDev]
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "David" wrote:
> > Hello,
> >
> > I am running a very simple command to grant a stored procedure an EXEC
> > permission to a login that is created for a NT group. The command is as
> > follows
> >
> > GRANT EXECUTE ON [dbo].[sp_RptUsersData] TO CORP\AppDev.
> >
> > I am getting the error like "Incorrect syntax near '\' "
> >
> > Please note that same name exist for login and database user i.e CORP\AppDev.
> >
> > Any help in this matter would be greatly appreciated.
> >
> >

Global DB Search Util

I've written a small search util that displays all tables/columns that
contain the given search string.
EXEC usp_GlobalFindText 'MyText'
The problem is that EXEC(@.STR) can only return a @.@.ERROR code, I Tried:
RETURN EXEC(@.SQL) --@.SQL = SELECT COUNT(*) FROM Table WHERE ID=5
This always returns 0 because no error is thrown...@.@.ERROR = 0
Is there a more elegant way search every table/Text column?
--Billg_sd
--SOURCE CODE--
CREATE PROCEDURE dbo.usp_GetRowResult
(
@.ROWID INT,
@.VAL INT OUTPUT
)
AS
DECLARE @.SQL VARCHAR(180)
SELECT @.SQL = [Query] FROM SEARCH WHERE RowID = @.ROWID
EXEC(@.SQL)
IF @.@.ERROR <> 0
SET @.VAL = 1
ELSE
SET @.VAL = 0
RETURN
GO
CREATE PROCEDURE dbo.usp_GlobalFindText
(
@.PATTERN VARCHAR(64)
)
/* WITH ENCRYPTION */
AS
SET NOCOUNT ON
DECLARE @.ICOUNT INT
DECLARE @.TOPROWINDEX INT
DECLARE @.RETVAL INT
DECLARE @.ROWID INT
--DECLARE @.PATTERN VARCHAR(64)
--EXTRACT TEXT FIELDS TO SEARCH
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SEARCH
')
DROP TABLE SEARCH
SELECT
IDENTITY(INT, 1, 1) AS RowID,
TABLE_NAME,
COLUMN_NAME,
QUERY = 'IF EXISTS(SELECT * FROM ' + TABLE_NAME +
' WHERE ' + COLUMN_NAME + ' LIKE ' + char(39) + '%' + @.PATTERN
+ '%' + char(39) + ') RAISERROR('''',11,1) ' ,
CAST(0 AS INT) AS TOTAL
INTO SEARCH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('char','nvarchar','ntext','varchar', 'text')
ORDER BY TABLE_NAME, COLUMN_NAME
--SEARCH FIELDS
SELECT @.ICOUNT = MIN(ROWID) FROM SEARCH
SELECT @.TOPROWINDEX = MAX(ROWID) FROM SEARCH
WHILE @.ICOUNT <= @.TOPROWINDEX
BEGIN
SELECT @.ROWID = ROWID FROM SEARCH WHERE ROWID = @.ICOUNT
IF @.ICOUNT IS NULL
CONTINUE
EXEC usp_GetRowResult @.ROWID, @.RETVAL OUTPUT
--PRINT '@.RETVAL = ' + CAST(@.RETVAL AS VARCHAR(10))
UPDATE SEARCH
SET TOTAL = @.RETVAL
WHERE RowID = @.ROWID
SET @.ICOUNT = @.ICOUNT + 1
IF @.ICOUNT <= @.TOPROWINDEX
CONTINUE
ELSE
BREAK
END
--Display results
SELECT TABLE_NAME, COLUMN_NAME FROM SEARCH WHERE TOTAL <> 0
GOVyas has a great SP on his website
http://vyaskn.tripod.com/
"Billg_sd" <Billgsd@.discussions.microsoft.com> wrote in message
news:CDF6DF52-2341-4377-A737-7FC4CFC75E31@.microsoft.com...
> I've written a small search util that displays all tables/columns that
> contain the given search string.
> EXEC usp_GlobalFindText 'MyText'
> The problem is that EXEC(@.STR) can only return a @.@.ERROR code, I Tried:
> RETURN EXEC(@.SQL) --@.SQL = SELECT COUNT(*) FROM Table WHERE ID=5
> This always returns 0 because no error is thrown...@.@.ERROR = 0
> Is there a more elegant way search every table/Text column?
> --Billg_sd
> --SOURCE CODE--
> CREATE PROCEDURE dbo.usp_GetRowResult
> (
> @.ROWID INT,
> @.VAL INT OUTPUT
> )
> AS
> DECLARE @.SQL VARCHAR(180)
> SELECT @.SQL = [Query] FROM SEARCH WHERE RowID = @.ROWID
> EXEC(@.SQL)
> IF @.@.ERROR <> 0
> SET @.VAL = 1
> ELSE
> SET @.VAL = 0
> RETURN
> GO
> CREATE PROCEDURE dbo.usp_GlobalFindText
> (
> @.PATTERN VARCHAR(64)
> )
> /* WITH ENCRYPTION */
> AS
> SET NOCOUNT ON
> DECLARE @.ICOUNT INT
> DECLARE @.TOPROWINDEX INT
> DECLARE @.RETVAL INT
> DECLARE @.ROWID INT
> --DECLARE @.PATTERN VARCHAR(64)
> --EXTRACT TEXT FIELDS TO SEARCH
> IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME =
'SEARCH')
> DROP TABLE SEARCH
> SELECT
> IDENTITY(INT, 1, 1) AS RowID,
> TABLE_NAME,
> COLUMN_NAME,
> QUERY = 'IF EXISTS(SELECT * FROM ' + TABLE_NAME +
> ' WHERE ' + COLUMN_NAME + ' LIKE ' + char(39) + '%' + @.PATTERN
> + '%' + char(39) + ') RAISERROR('''',11,1) ' ,
> CAST(0 AS INT) AS TOTAL
> INTO SEARCH
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE DATA_TYPE IN ('char','nvarchar','ntext','varchar', 'text')
> ORDER BY TABLE_NAME, COLUMN_NAME
> --SEARCH FIELDS
> SELECT @.ICOUNT = MIN(ROWID) FROM SEARCH
> SELECT @.TOPROWINDEX = MAX(ROWID) FROM SEARCH
> WHILE @.ICOUNT <= @.TOPROWINDEX
> BEGIN
> SELECT @.ROWID = ROWID FROM SEARCH WHERE ROWID = @.ICOUNT
> IF @.ICOUNT IS NULL
> CONTINUE
> EXEC usp_GetRowResult @.ROWID, @.RETVAL OUTPUT
> --PRINT '@.RETVAL = ' + CAST(@.RETVAL AS VARCHAR(10))
> UPDATE SEARCH
> SET TOTAL = @.RETVAL
> WHERE RowID = @.ROWID
> SET @.ICOUNT = @.ICOUNT + 1
> IF @.ICOUNT <= @.TOPROWINDEX
> CONTINUE
> ELSE
> BREAK
> END
> --Display results
> SELECT TABLE_NAME, COLUMN_NAME FROM SEARCH WHERE TOTAL <> 0
> GO
>
>|||This website is huge. Where can I find this sample?
"Uri Dimant" wrote:

> Vyas has a great SP on his website
> http://vyaskn.tripod.com/
>