Bit of an emergency!
I do not have direct access to our SQL Server but I have full FTP access to the web server and have the db Username/passwords.
I need to grant execute permissions on a stored procedure, can I do this from an asp/ASP.NET page?
The DB guys take 24 hours to run a script against the database!
Any help would be greatfully recieved.
Rich
You can use a SqlCommand object, and set the command text to something like this:
Grant Execute Onsp_name To ASPNET
Then use .ExecuteNonQuery()
Showing posts with label execute. Show all posts
Showing posts with label execute. Show all posts
Thursday, March 29, 2012
Granting right to all the user sp's
Hello,
I would like to ease granting the right to execute all user sp's by normal u
sers.
By default, AFIK you have to click the EXECUTE option of each and every sp.
I head about a procedure, which could automate this.
Anyone knows this kind of procedure or how to to this?
Thank You
JoachimHi,
use pubs
go
select 'grant execute on ' +name +' to user_name' from sysobjects where
type='p'
Replace the database name and user_name based on your requirement.
After excuting the script you will get a bunch of grant statement in your
result pane, just cut and paste the entire contents and execute it again.
Thanks
Hari
MCDBA
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim|||You can use a procedure like this
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)
-- check user exists
if not exists(select * from sysusers where name = @.user)
begin
raiserror('User %s is not a valid user in this database',16,1,@.user)
return -1
end
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 -2
end
go
Then you can run it like below to grant user foo exec permissions on all
user stored procedures in the pubs database
use pubs
go
exec sp_grantexec 'foo'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim
I would like to ease granting the right to execute all user sp's by normal u
sers.
By default, AFIK you have to click the EXECUTE option of each and every sp.
I head about a procedure, which could automate this.
Anyone knows this kind of procedure or how to to this?
Thank You
JoachimHi,
use pubs
go
select 'grant execute on ' +name +' to user_name' from sysobjects where
type='p'
Replace the database name and user_name based on your requirement.
After excuting the script you will get a bunch of grant statement in your
result pane, just cut and paste the entire contents and execute it again.
Thanks
Hari
MCDBA
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim|||You can use a procedure like this
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)
-- check user exists
if not exists(select * from sysusers where name = @.user)
begin
raiserror('User %s is not a valid user in this database',16,1,@.user)
return -1
end
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 -2
end
go
Then you can run it like below to grant user foo exec permissions on all
user stored procedures in the pubs database
use pubs
go
exec sp_grantexec 'foo'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:404C5069.C80FF06E@.freenet.de...
> Hello,
> I would like to ease granting the right to execute all user sp's by normal
users.
> By default, AFIK you have to click the EXECUTE option of each and every
sp.
> I head about a procedure, which could automate this.
> Anyone knows this kind of procedure or how to to this?
> Thank You
> Joachim
Granting permissions to xp_regread
Is it possible to grant execute permissions to xp_regread to a user who isn'
t
a member of the sysadmins role?Public has execute permissions on xp_regread so why would
you need to? But yes...you can grant execute.
-Sue
On Tue, 18 Oct 2005 20:28:02 -0700, "David"
<David@.discussions.microsoft.com> wrote:
>Is it possible to grant execute permissions to xp_regread to a user who isn
't
>a member of the sysadmins role?
t
a member of the sysadmins role?Public has execute permissions on xp_regread so why would
you need to? But yes...you can grant execute.
-Sue
On Tue, 18 Oct 2005 20:28:02 -0700, "David"
<David@.discussions.microsoft.com> wrote:
>Is it possible to grant execute permissions to xp_regread to a user who isn
't
>a member of the sysadmins role?
Granting Permissions to a stored procedure
I have a stored procedure sp_LogError that it self executes the
xp_cmdShell procedure.
When I execute the procedure using a 'normal' acount with only public
acces to the database i get an error:
EXECUTE permission denied on object 'xp_cmdshell', Database 'master',
owner 'dbo'
sp_LogError is owned by dbo and all users have permission to access it.
It runs fine under the sa account.
What do I need to do so that all users can execute sp_LogError without
granting all users access to xp_cmdShell.
Thanks In Advance
MalachyHi,
You need to give exclusive previlage to xp_cmdshell proc to user if he is
not the member of sy
min role.
Execute permissions for xp_cmdshell default to members of the symin fixed
server role, but can be granted to other users.
Note:
If you choose to use a Windows NT account that is not a member of the local
administrator's group to start MSSQLServer service, users who
are not members of the symin fixed server role cannot execute xp_cmdshell
Thanks
Hari
SQL Serber MVP
"Malachy O'Connor" <malachyoconnor2@.o2.ie> wrote in message
news:1113392109.360561.53050@.g14g2000cwa.googlegroups.com...
>I have a stored procedure sp_LogError that it self executes the
> xp_cmdShell procedure.
> When I execute the procedure using a 'normal' acount with only public
> acces to the database i get an error:
> EXECUTE permission denied on object 'xp_cmdshell', Database 'master',
> owner 'dbo'
> sp_LogError is owned by dbo and all users have permission to access it.
> It runs fine under the sa account.
> What do I need to do so that all users can execute sp_LogError without
> granting all users access to xp_cmdShell.
> Thanks In Advance
> Malachy
>|||Thanks for that Hari.
It is just that I was hoping not to have to give access to xp_cmdshell
to all users that wished to use my sp. I|||I was able to create my own sp_logError in master which called the
xp_cmdShell. Because it was owned by dbo it had permission to execute
xp_cmdShell.
I was then able to make sp_logError public so everyone can access it
without needing explicit access to xp_cmdShell.
xp_cmdShell procedure.
When I execute the procedure using a 'normal' acount with only public
acces to the database i get an error:
EXECUTE permission denied on object 'xp_cmdshell', Database 'master',
owner 'dbo'
sp_LogError is owned by dbo and all users have permission to access it.
It runs fine under the sa account.
What do I need to do so that all users can execute sp_LogError without
granting all users access to xp_cmdShell.
Thanks In Advance
MalachyHi,
You need to give exclusive previlage to xp_cmdshell proc to user if he is
not the member of sy
min role.Execute permissions for xp_cmdshell default to members of the sy
min fixedserver role, but can be granted to other users.
Note:
If you choose to use a Windows NT account that is not a member of the local
administrator's group to start MSSQLServer service, users who
are not members of the sy
min fixed server role cannot execute xp_cmdshellThanks
Hari
SQL Serber MVP
"Malachy O'Connor" <malachyoconnor2@.o2.ie> wrote in message
news:1113392109.360561.53050@.g14g2000cwa.googlegroups.com...
>I have a stored procedure sp_LogError that it self executes the
> xp_cmdShell procedure.
> When I execute the procedure using a 'normal' acount with only public
> acces to the database i get an error:
> EXECUTE permission denied on object 'xp_cmdshell', Database 'master',
> owner 'dbo'
> sp_LogError is owned by dbo and all users have permission to access it.
> It runs fine under the sa account.
> What do I need to do so that all users can execute sp_LogError without
> granting all users access to xp_cmdShell.
> Thanks In Advance
> Malachy
>|||Thanks for that Hari.
It is just that I was hoping not to have to give access to xp_cmdshell
to all users that wished to use my sp. I|||I was able to create my own sp_logError in master which called the
xp_cmdShell. Because it was owned by dbo it had permission to execute
xp_cmdShell.
I was then able to make sp_logError public so everyone can access it
without needing explicit access to xp_cmdShell.
Granting permissions
How can I do
create proc MyProc
as
--...proc logic
go
grant execute on MyProc to MYCOMPUTER\ASPNET
I can do the 'grant' statement where the user name doesn't include a
computer prefix - but the ASPNET account does! It keeps complaining, citing
'Incorrect syntax near \'.
The following doesn't work either.
grant execute on MyProc to 'MYCOMPUTER\ASPNET'
Any suggestions?Bonj,
I think MYCOMPUTER\ASPNET is the login name, What is the user name
associated to this login in your db?
AMB
"Bonj" wrote:
> How can I do
> create proc MyProc
> as
> --...proc logic
> go
> grant execute on MyProc to MYCOMPUTER\ASPNET
> I can do the 'grant' statement where the user name doesn't include a
> computer prefix - but the ASPNET account does! It keeps complaining, citin
g
> 'Incorrect syntax near '.
> The following doesn't work either.
> grant execute on MyProc to 'MYCOMPUTER\ASPNET'
>
> Any suggestions?|||assuming MYCOMPUTER\ASPNET is a defined login, then:
grant execute on MyProc to "MYCOMPUTER\\ASPNET"
note double quotes
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:C4F7742C-22E5-4FEB-BE1B-3570FC0645B0@.microsoft.com...
| How can I do
|
| create proc MyProc
| as
| --...proc logic
| go
| grant execute on MyProc to MYCOMPUTER\ASPNET
|
| I can do the 'grant' statement where the user name doesn't include a
| computer prefix - but the ASPNET account does! It keeps complaining,
citing
| 'Incorrect syntax near \'.
| The following doesn't work either.
| grant execute on MyProc to 'MYCOMPUTER\ASPNET'
|
|
| Any suggestions?sql
create proc MyProc
as
--...proc logic
go
grant execute on MyProc to MYCOMPUTER\ASPNET
I can do the 'grant' statement where the user name doesn't include a
computer prefix - but the ASPNET account does! It keeps complaining, citing
'Incorrect syntax near \'.
The following doesn't work either.
grant execute on MyProc to 'MYCOMPUTER\ASPNET'
Any suggestions?Bonj,
I think MYCOMPUTER\ASPNET is the login name, What is the user name
associated to this login in your db?
AMB
"Bonj" wrote:
> How can I do
> create proc MyProc
> as
> --...proc logic
> go
> grant execute on MyProc to MYCOMPUTER\ASPNET
> I can do the 'grant' statement where the user name doesn't include a
> computer prefix - but the ASPNET account does! It keeps complaining, citin
g
> 'Incorrect syntax near '.
> The following doesn't work either.
> grant execute on MyProc to 'MYCOMPUTER\ASPNET'
>
> Any suggestions?|||assuming MYCOMPUTER\ASPNET is a defined login, then:
grant execute on MyProc to "MYCOMPUTER\\ASPNET"
note double quotes
"Bonj" <Bonj@.discussions.microsoft.com> wrote in message
news:C4F7742C-22E5-4FEB-BE1B-3570FC0645B0@.microsoft.com...
| How can I do
|
| create proc MyProc
| as
| --...proc logic
| go
| grant execute on MyProc to MYCOMPUTER\ASPNET
|
| I can do the 'grant' statement where the user name doesn't include a
| computer prefix - but the ASPNET account does! It keeps complaining,
citing
| 'Incorrect syntax near \'.
| The following doesn't work either.
| grant execute on MyProc to 'MYCOMPUTER\ASPNET'
|
|
| Any suggestions?sql
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...
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...
Tuesday, March 27, 2012
Grant using Query
Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
Thanks in advance,
Steven
Hi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
Thanks in advance,
Steven
Hi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>
Grant using Query
Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>sql
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>sql
Grant using Query
Hello,
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>
I am using the following to create the text for granting permissions to
stored procedures:
USE Train
SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
Is it possible to actually grant permissions by using an SQL statement like
the following?
GRANT EXECUTE ON
SELECT name
FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
'%web_%')
TO Web_Publish
--
Thanks in advance,
StevenHi
DECLARE @.proc_name SYSNAME
DECLARE @.sql VARCHAR(4000)
SET @.proc_name = ''
WHILE 1=1
BEGIN
SET @.proc_name = (SELECT TOP 1 ROUTINE_NAME FROM
INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'IsMSShipped') = 0
-- Only user stored procedures
AND ROUTINE_TYPE = 'Procedure'
AND ROUTINE_NAME > @.proc_name
ORDER BY ROUTINE_NAME
)
IF @.proc_name IS NULL BREAK
SET @.sql = 'GRANT EXECUTE ON ' + QUOTENAME(@.proc_name) + ' TO MyUser'
EXEC (@.sql)
END
"Steven K0" <stroy@.api.com> wrote in message
news:%23KrhfbRNGHA.3944@.tk2msftngp13.phx.gbl...
> Hello,
> I am using the following to create the text for granting permissions to
> stored procedures:
> USE Train
> SELECT 'GRANT EXECUTE ON '+ name + ' TO Web_Publish'
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> Is it possible to actually grant permissions by using an SQL statement
> like the following?
> GRANT EXECUTE ON
> SELECT name
> FROM sysobjects WHERE (type = 'P') AND (category = 0) AND (name LIKE
> '%web_%')
> TO Web_Publish
> --
> Thanks in advance,
> Steven
>
>
Monday, March 26, 2012
Grant job view and execute
Hello,
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>|||Actually, i need non-system administrators users could view and execute a
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>|||Actually, i need non-system administrators users could view and execute a
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>
Grant job view and execute
Hello,
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
quote:|||Actually, i need non-system administrators users could view and execute a
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
quote:sql
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>
Grant Execute!
Hi all,
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
AdamIf you have SQL Server 2005, you can GRANT EXECUTE on a schema. If you have
SQL 2000, you'd have to do the GRANT's separately. Ideally, you should
grant only to a role and then add users to the role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uMljqe%23PGHA.2888@.tk2msftngp13.phx.gbl...
Hi all,
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
Adam|||Hi,
http://www.codeproject.com/database/T-SQL.asp
HTH, Jens Suessmeyer.|||Run this in the database, then copy the results to the query window
and execute.
select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'
Roy Harvey
Beacon Falls, CT
On Sun, 5 Mar 2006 09:59:09 +1000, "Mr Ideas Man"
<adam@.pertrain.com.au> wrote:
>Hi all,
>One of my workmates has acciddently changed the EXECUTE permissions for my
>main login.
>Is their a System stored procedure or something i can do to give Execute
>permissions on all stored procedures in my DB
>to a particular user, without having to do each procedure individually.
>Cheers,
>Adam
>
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
AdamIf you have SQL Server 2005, you can GRANT EXECUTE on a schema. If you have
SQL 2000, you'd have to do the GRANT's separately. Ideally, you should
grant only to a role and then add users to the role.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uMljqe%23PGHA.2888@.tk2msftngp13.phx.gbl...
Hi all,
One of my workmates has acciddently changed the EXECUTE permissions for my
main login.
Is their a System stored procedure or something i can do to give Execute
permissions on all stored procedures in my DB
to a particular user, without having to do each procedure individually.
Cheers,
Adam|||Hi,
http://www.codeproject.com/database/T-SQL.asp
HTH, Jens Suessmeyer.|||Run this in the database, then copy the results to the query window
and execute.
select 'Grant EXEC on ' + name + ' to WhomEver'
from sysobjects
where type = 'P'
Roy Harvey
Beacon Falls, CT
On Sun, 5 Mar 2006 09:59:09 +1000, "Mr Ideas Man"
<adam@.pertrain.com.au> wrote:
>Hi all,
>One of my workmates has acciddently changed the EXECUTE permissions for my
>main login.
>Is their a System stored procedure or something i can do to give Execute
>permissions on all stored procedures in my DB
>to a particular user, without having to do each procedure individually.
>Cheers,
>Adam
>
Grant Execute to user on procedures
Hi,
is there a easy way of grantinng Execute-privilegies to user ABC for all
procedures named 'MTS*' in a database?
regards,
Bent S. Lund
System Developer
MCP VB
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
In Query Analyzer execute the below script with Text result.
Use <dbname>
go
select 'grant execute on '+name +' to ABC' from sysobjects where name like
'MTS%' and type='P'
-- The above script will generate a script to grant execute previlage to ABC
user for all procedures start with MTS%.
Copy the result window and paste in a new Query analyzer window and execute
it.
Thanks
Hari
MCDBA
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Have a look at
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
and sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
You can use this like
exec sp_grantexec 'ABC','MTS%'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
is there a easy way of grantinng Execute-privilegies to user ABC for all
procedures named 'MTS*' in a database?
regards,
Bent S. Lund
System Developer
MCP VB
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!Hi,
In Query Analyzer execute the below script with Text result.
Use <dbname>
go
select 'grant execute on '+name +' to ABC' from sysobjects where name like
'MTS%' and type='P'
-- The above script will generate a script to grant execute previlage to ABC
user for all procedures start with MTS%.
Copy the result window and paste in a new Query analyzer window and execute
it.
Thanks
Hari
MCDBA
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Have a look at
Granting execute permissions to all stored procedures in a database
http://www.sqldbatips.com/showarticle.asp?ID=8
and sp_grantexec
http://www.sqldbatips.com/showcode.asp?ID=2
You can use this like
exec sp_grantexec 'ABC','MTS%'
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Bent Lund" <bstlu@.online.no> wrote in message
news:ucjryKQYEHA.3012@.tk2msftngp13.phx.gbl...
> Hi,
> is there a easy way of grantinng Execute-privilegies to user ABC for all
> procedures named 'MTS*' in a database?
>
> regards,
> Bent S. Lund
> System Developer
> MCP VB
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
Grant execute on XP_CMDSHELL indirectly
I need to grant execute permissions to some users on xp_cmdshell. I am
trying to avoid having to grant execute permission directly on
xp_cmdhell, but alternatively grant access indirectly through another
procedure. The idea will be to write another proc "Custom_cmdshell"
which will call xp_cmdshell.
I am familiar with ownership chains but haven't figured out a way to
work with it in this situation.
Regards,
KenOn Feb 26, 5:37=A0am, Ken <raid...@.yahoo.com> wrote:
> I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through =A0another
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken
Please refere to below article .
http://support.microsoft.com/kb/890775/en-us
Thanks
Ajay Rengunthwar
MCTS|||I think that this article might contain valuable information and possibly options for you:
http://www.sommarskog.se/grantperm.html
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ken" <raidken@.yahoo.com> wrote in message
news:afad664c-55a0-4ffd-b1f1-0037373d258e@.q33g2000hsh.googlegroups.com...
>I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through another
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken|||I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47=A0am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly =options for you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
>|||Check out the section "Cross-Database Access", which is applicable to 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ken" <raidken@.yahoo.com> wrote in message
news:0d244ac3-57ee-430e-a612-1d7dcc4d9d47@.e60g2000hsh.googlegroups.com...
I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly options for
> you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>sql
trying to avoid having to grant execute permission directly on
xp_cmdhell, but alternatively grant access indirectly through another
procedure. The idea will be to write another proc "Custom_cmdshell"
which will call xp_cmdshell.
I am familiar with ownership chains but haven't figured out a way to
work with it in this situation.
Regards,
KenOn Feb 26, 5:37=A0am, Ken <raid...@.yahoo.com> wrote:
> I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through =A0another
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken
Please refere to below article .
http://support.microsoft.com/kb/890775/en-us
Thanks
Ajay Rengunthwar
MCTS|||I think that this article might contain valuable information and possibly options for you:
http://www.sommarskog.se/grantperm.html
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ken" <raidken@.yahoo.com> wrote in message
news:afad664c-55a0-4ffd-b1f1-0037373d258e@.q33g2000hsh.googlegroups.com...
>I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through another
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken|||I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47=A0am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly =options for you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph=
ttp://sqlblog.com/blogs/tibor_karaszi
>|||Check out the section "Cross-Database Access", which is applicable to 2000.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ken" <raidken@.yahoo.com> wrote in message
news:0d244ac3-57ee-430e-a612-1d7dcc4d9d47@.e60g2000hsh.googlegroups.com...
I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly options for
> you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server
> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>sql
Grant execute on XP_CMDSHELL indirectly
I need to grant execute permissions to some users on xp_cmdshell. I am
trying to avoid having to grant execute permission directly on
xp_cmdhell, but alternatively grant access indirectly through another
procedure. The idea will be to write another proc "Custom_cmdshell"
which will call xp_cmdshell.
I am familiar with ownership chains but haven't figured out a way to
work with it in this situation.
Regards,
Ken
On Feb 26, 5:37Xam, Ken <raid...@.yahoo.com> wrote:
> I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through Xanother
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken
Please refere to below article .
http://support.microsoft.com/kb/890775/en-us
Thanks
Ajay Rengunthwar
MCTS
|||I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47Xam, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly options for you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
trying to avoid having to grant execute permission directly on
xp_cmdhell, but alternatively grant access indirectly through another
procedure. The idea will be to write another proc "Custom_cmdshell"
which will call xp_cmdshell.
I am familiar with ownership chains but haven't figured out a way to
work with it in this situation.
Regards,
Ken
On Feb 26, 5:37Xam, Ken <raid...@.yahoo.com> wrote:
> I need to grant execute permissions to some users on xp_cmdshell. I am
> trying to avoid having to grant execute permission directly on
> xp_cmdhell, but alternatively grant access indirectly through Xanother
> procedure. The idea will be to write another proc "Custom_cmdshell"
> which will call xp_cmdshell.
> I am familiar with ownership chains but haven't figured out a way to
> work with it in this situation.
> Regards,
> Ken
Please refere to below article .
http://support.microsoft.com/kb/890775/en-us
Thanks
Ajay Rengunthwar
MCTS
|||I should have mentioned that we are using SQL 2000. The article
mentioned applies to SQL 2005 only.
Ken
On Feb 26, 3:47Xam, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> I think that this article might contain valuable information and possibly options for you:http://www.sommarskog.se/grantperm.html
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>
Grant execute on many procs in 2005
I need to grant execute permissions to many stored procs to a user. How do
you accomplish this in 2005?Cheep and cheerfull method: -
execute
select 'GRANT EXECUTE ON ' + ROUTINE_NAME + ' TO <<YourUser>>' from
information_schema.routines
grab result from results pane and execute them
Paul
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:3ABC3458-7AC0-4D8F-8C63-8CCB4CB9757B@.microsoft.com...
>I need to grant execute permissions to many stored procs to a user. How do
> you accomplish this in 2005?
you accomplish this in 2005?Cheep and cheerfull method: -
execute
select 'GRANT EXECUTE ON ' + ROUTINE_NAME + ' TO <<YourUser>>' from
information_schema.routines
grab result from results pane and execute them
Paul
"Andre" <Andre@.discussions.microsoft.com> wrote in message
news:3ABC3458-7AC0-4D8F-8C63-8CCB4CB9757B@.microsoft.com...
>I need to grant execute permissions to many stored procs to a user. How do
> you accomplish this in 2005?
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.
>
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.
>
Sunday, February 26, 2012
GO Statement
Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
Michael
Michael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
Michael
Michael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>
GO Statement
Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>
GO Statement
Hi,
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>
I've notied when I execute SQL Transact statements I can do it without
GO Statement. But when I refer to a lot of online documents, I see GO
statements. So from the practice/good coding perspective, when shall I
put GO statement? Why?
Thanks a lot!!
MichaelMichael,
Did you look into BOL before posting the question?
GO (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms188037.aspx
AMB
"Michael" wrote:
> Hi,
> I've notied when I execute SQL Transact statements I can do it without
> GO Statement. But when I refer to a lot of online documents, I see GO
> statements. So from the practice/good coding perspective, when shall I
> put GO statement? Why?
> Thanks a lot!!
> Michael
>
Subscribe to:
Posts (Atom)