Granting xp_cmdshell permission to SQL Login

Hi database geeks and MVPs!
Using SQL Server 2005 SP2
I have a stored procedure in my database which calls xp_cmdshell to run a
little task. I have done the following to allow this proc to be executed by
a
non-privileged user:
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
I get the following error:
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
through via a stored procedure?
Thanks,
Mark.Mark
Did you restart MSSQLSERVICE after granting permissions?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Yes I did. No effect.
"Uri Dimant" wrote:

> Mark
> Did you restart MSSQLSERVICE after granting permissions?
>|||Hello Mark,
You can't use xp_cmdshell because you do not enable it. Because of the
'allow updates' option.
Your 'allow updates' server setting must be enabled. If you want to leave it
enabled then you'll need to run your code as the following to enable
xp_cmdshell
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
GO
If you disable your 'allow updates' option using the following code
EXEC sp_configure 'allow updates', 0
GO
RECONFIGURE WITH OVERRIDE
Then you'll be able to run your code as it is (without with override
thing...)
P.S.
I learned this solution from Jasper Smith, thanks to him.
Ekrem ?nsoy
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:F3731867-69A7-462A-8023-4DC8B7A1BE78@.microsoft.com...
> Hi database geeks and MVPs!
> Using SQL Server 2005 SP2
> I have a stored procedure in my database which calls xp_cmdshell to run a
> little task. I have done the following to allow this proc to be executed
> by a
> non-privileged user:
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> I get the following error:
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> Why is that? Is it possible to allow a SQL Login to execute xp_cmdshell
> through via a stored procedure?
> Thanks,
> Mark.|||Mark
xp_cmdshell requires CONTROL SERVER permission.
Does the user have it?
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
> Yes I did. No effect.
> "Uri Dimant" wrote:
>
>|||Hi Uri,
I have changed my script to incorporate that permission, but I still get the
same error. xp_cmdshell works fine when run as a sysadmin.
USE MASTER
GO
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
N'mysqllogin')
CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GRANT EXECUTE ON xp_cmdshell TO mysqllogin
--EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
doesn't work either
GRANT CONTROL SERVER TO mysqllogin
CREATE DATABASE mytestdb
GO
USE mytestdb
GO
CREATE USER mysqllogin FROM LOGIN mysqllogin
GO
CREATE PROC exec_xpcmdshell
AS
EXEC MASTER.dbo.xp_cmdshell 'dir c:'
GO
GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
EXECUTE AS USER = 'mysqllogin'
GO
EXEC [exec_xpcmdshell]
GO
revert
go
/*
Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
An error occurred during the execution of xp_cmdshell. A call to
'LogonUserW' failed with error code: '1329'.
*/
"Uri Dimant" wrote:

> Mark
> xp_cmdshell requires CONTROL SERVER permission.
> Does the user have it?
>
>
>
>
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:4B55021C-5F24-4D1A-881B-24A903835029@.microsoft.com...
>
>|||When I type "NET HELPMSG 1329" from the command prompt, I get message "Logon
failure: user not allowed to log on to this computer."
Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
has permissions to login locally.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:FF048394-0029-4E1B-931F-E6B9E2F9CFF3@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> I have changed my script to incorporate that permission, but I still get
> the
> same error. xp_cmdshell works fine when run as a sysadmin.
> USE MASTER
> GO
> EXEC sp_configure 'xp_cmdshell', 1
> RECONFIGURE
> GO
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname =
> N'mysqllogin')
> CREATE LOGIN [mysqllogin] WITH PASSWORD = 'myPa55word'
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GRANT EXECUTE ON xp_cmdshell TO mysqllogin
> --EXEC sp_xp_cmdshell_proxy_account 'mysqllogin','myPa55word' -- this
> doesn't work either
> GRANT CONTROL SERVER TO mysqllogin
>
> CREATE DATABASE mytestdb
> GO
> USE mytestdb
> GO
> CREATE USER mysqllogin FROM LOGIN mysqllogin
> GO
> CREATE PROC exec_xpcmdshell
> AS
> EXEC MASTER.dbo.xp_cmdshell 'dir c:'
> GO
> GRANT EXECUTE ON exec_xpcmdshell TO mysqllogin
> EXECUTE AS USER = 'mysqllogin'
> GO
> EXEC [exec_xpcmdshell]
> GO
> revert
> go
> /*
> Msg 15121, Level 16, State 200, Procedure xp_cmdshell, Line 1
> An error occurred during the execution of xp_cmdshell. A call to
> 'LogonUserW' failed with error code: '1329'.
> */
>
> "Uri Dimant" wrote:
>|||Hi Dan,
That's the point. I cannot grant access to a SQL Login. It works fine with a
Windows login, but I have a SQL Login.
From BOL:
sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'password'
} ]
Arguments
NULL
Specifies that the proxy credential should be deleted.
account_name
Specifies a Windows login that will be the proxy.
Mark.
"Dan Guzman" wrote:

> When I type "NET HELPMSG 1329" from the command prompt, I get message "Log
on
> failure: user not allowed to log on to this computer."
> Make sure the proxy account (configured with sp_xp_cmdshell_proxy_account)
> has permissions to login locally.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
>|||> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
xp_cmdshell needs an OS security context when it runs. That security
context is the Windows xp_cmdshell proxy account when it's executed by a
non-sysadmin user. The 1329 error isn't related to the SQL login executing
xp_cmdshell but is rather the Windows error code returned because the
xp_cmdshell proxy account doesn't have the needed Windows permissions
My guess is that the Windows login you mentioned is a sysadmin role member.
Xp_cmdshell runs under the context of the SQL Server service account when
executed by a sysadmin role member and that account probably has different
permissions than the proxy account
I successfully ran a modified of your original script on my test system. My
xp_cmdshell proxy account is a minimally privileged domain user account and
. I didn't grant CONTROL SERVER permission..
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:61EA5CCA-4C65-410C-959B-7B67195F2DFD@.microsoft.com...[vbcol=seagreen]
> Hi Dan,
> That's the point. I cannot grant access to a SQL Login. It works fine with
> a
> Windows login, but I have a SQL Login.
> From BOL:
> sp_xp_cmdshell_proxy_account [ NULL | { 'account_name' , 'passwor
d' } ]
> Arguments
> NULL
> Specifies that the proxy credential should be deleted.
> account_name
> Specifies a Windows login that will be the proxy.
>
> Mark.
> "Dan Guzman" wrote:
>

Granting stored procedure execute permissions from ASP.NET?

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()

Granting permissions to stored procedures

I am using the following code to grant user access to the stored procedures in my database. However, it does not appear to be working because I am getting an access denied message when running the application as the user.

Here is the code I am using:

Code Snippet

' Grant privileges

Dim ExecutePrivilege As New ObjectPermissionSet

ExecutePrivilege.Execute = True

' Grant privileges to all non-system stored procs

' The following line improves performance

SmoServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")

For Each sp As StoredProcedure In db.StoredProcedures

If Not sp.IsSystemObject Then

sp.Grant(ExecutePrivilege, loginName)

End If

Next

Is there something else I need to do, like a Save or Refresh or something? (I've stepped through the code and it *is* executing for each of my stored procedures. It just does not appear to actually have updated the priviledge.)

Any tips or ideas would be appreciated.

Thanks!

Security related commands are always executed directly, unless you change the setting from the context to only script the commands like the following statement does:

svr.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

The reflected sources show that a property is set for this which is called

so.ForDirectExecution = true;

Did you have a look in the profiler to see if the commands are arriving at the server and are eventually bounced back due to errors occuring during applying the script ?

Jens K. Suessmeyer

http://www.sqlserver2005.de|||

I skipped the profiler and went right to the database. I can view permissions, and they are actually set correctly. So the code is executing. (Should have thought of that before I posted!<G>)

The problem is that when I try to access any of the stored procs (or the tables), I get a "permission was denied on the object" message. So something else is obviously wrong.

This particular code happens to be in VB6, accessing SQLServer Express. When the *same* code accesses a SQL Server 2000 database, it runs without this error.

Any idea what could be wrong here? Or at this point do I need to move the question elsewhere since it does not appear to be an SMO problem.

sql

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 symin 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.

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
Sam
Think I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam
|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, an
d
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, a
s
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also somethin
g
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:

> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs p
er
> table, this is too time consuming. I could script, but this is also someth
ing
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permissions

I have a production server with a few databases. Each database has about
100-150 tables, and 200 or so Stored Procs.
I have a new user john that gets created on each database. I need to give
him the ability to select, insert, update and delete from any user table, and
to be able to execute any Stored Procedure.
If I grant this new user membership to db_datareader, he gets select from
any user table.
If I grant this new user membership to db_datawriter, he gets insert,
update, delete to any user table.
So far, so good. Now the problem -
What role can I grant that would allow this user to be able to run any
stored procedure? Note that my manager will not let me grant him db_owner, as
this gives him too much rights.
And what every I do to give him the above rights, he can't be allowed to
create tables, create views or create any stored procedures
I could do this manually through the GUI, but with over 200 stored procs per
table, this is too time consuming. I could script, but this is also something
labor intensive, am looking for an easier way.
Thank you in advance.
SamThink I found the solution. For each table, use the:
DENY CREATE VIEW to john
DENY CREATE TABLE to john
DENY CREATE SP to john
Still not sure how to give a user rights to execute all Stored Procedures,
though.
Sam
"Sam" wrote:
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table, and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner, as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs per
> table, this is too time consuming. I could script, but this is also something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam|||Create a new role in each database.
Grant the appropriate permissions to this role. (I know it's time
consuming, but it's a one time deal).
Then add your new users to this role in each db.
If you check around, there have been several scripts already created that
will grant permissions. You just need to tweak them a bit. My guess is
that in an hour worth of your time, you will have the script completed.
This newsgroup has had several posted to it within the last two weeks.
Rick Sawtell
MCT, MCSD, MCDBA
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:E7839D71-7EC7-47A0-96DF-4A62DAEED1D0@.microsoft.com...
> I have a production server with a few databases. Each database has about
> 100-150 tables, and 200 or so Stored Procs.
> I have a new user john that gets created on each database. I need to give
> him the ability to select, insert, update and delete from any user table,
and
> to be able to execute any Stored Procedure.
> If I grant this new user membership to db_datareader, he gets select from
> any user table.
> If I grant this new user membership to db_datawriter, he gets insert,
> update, delete to any user table.
> So far, so good. Now the problem -
> What role can I grant that would allow this user to be able to run any
> stored procedure? Note that my manager will not let me grant him db_owner,
as
> this gives him too much rights.
> And what every I do to give him the above rights, he can't be allowed to
> create tables, create views or create any stored procedures
> I could do this manually through the GUI, but with over 200 stored procs
per
> table, this is too time consuming. I could script, but this is also
something
> labor intensive, am looking for an easier way.
> Thank you in advance.
> Sam

Granting permission on multiple stored procs

Is there a better way of granting permission to a stored proc for a selected user other than (enterprise manager) select sp then accessing propertys then permissions, then user?GRANT EXEC ON <proc> TO <MyUser>

just about anything you can do in the EM can be scripted saved as script and used again. which makes you more efficient.|||but the rest of the story is this...
the project is a work in progress thing. we are added new stored procs as needed (up to 83 as of this morning) and now we wanted a selected user to test the new project. I was looking for a way to not have to type out all 83 stored proc names in order to grant access to them for this user. I was hoping for a solution/feature that i was not aware of in EM. so it looks like i will have to do this process manually either way (EM or Script)...|||select 'grant execute on ' + name + ' to user'
from sysobjects
where type = 'P'
and crdate > getdate() -1|||You are using Database Roles, I hope.

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 Edit Permission on Stored Procedures

I have a user on my database that has the following base permissions :

public
db_datareader

I need to give this user permission to edit a single stored procedure. I have tried using the following command :

GRANT ALL ON stored_procedure_name TO username

Which executes successfully, but the user still cannot edit the stored procedure.

If I give the user db_ddladmin permission they can edit all the user stored procedures, but for security reasons I would prefer to be able to this this at procedure level rather than a global permission on all user procs.

Does anybody know how I can do this?

EDIT : This is on SQL 2000Does this user have the same permission on some tables which related to this procedure?sql

Grant Win Acct Permission

I'd like to grant a WIndows account permission to connect to a db and
exec stored procedures. But am having trouble.

I want this type of effect but can't get the syntax correct:

USE MyDB
GO
CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
GO

GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET

How is this done in a t-sql script?

Thanks for any help.(wackyphill@.yahoo.com) writes:
> I'd like to grant a WIndows account permission to connect to a db and
> exec stored procedures. But am having trouble.
> I want this type of effect but can't get the syntax correct:
> USE MyDB
> GO
> CREATE USER 127.0.0.1\ASPNET --ASPNET Account for current machine
> GO
> GRANT EXECUTE ON AllStoredPRocs TO 127.0.0.1\ASPNET
> How is this done in a t-sql script?

Since 127.0.0.1\ASPNET is one identifier as far SQL Server is concerned,
you need to put it brackets: [127.0.0.1\ASPNET]. Then whether that
actually works is another matter. You should probably use machine name
rather than 127.0.0.1.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

Grant permissions

Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.db_datareader (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_6ndx.asp).

-PatPsql

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 object permissions in stored procedure

I am lazy. I thought that I would get this out in the open from the outset.
Now, that said, I will justify it. I like to set up little routines that do
all of the things that I keep forgetting to do, such as setting the
permissions on new stored procedures that I add to a database.
I have a problem however; I cannot use the 'grant execute on myobject to
user' with variables.
Consider the following taken from my database permissions setup script ...
...
while (@.@.fetch_status = 0) begin
-- Set the owner to dbo if not already ...
if (@.objectUid <> @.dboUid)
execute sp_changeobjectowner @.objname=@.objectName,
@.newowner='dbo'
-- Grant public access permission.
grant execute on @.objectName to public
fetch next from objectNames into @.objectName, @.objectUid
end
...
This code generates an invalid syntax error on the lise 'grant execute ...'.
I have also tried creating a variable containing the command with the
variables expanded and using exec[ute] to execute the command. This also
fails as exec[ute] "Executes a scalar-valued, user-defined function, a
system procedure, a user-defined stored procedure, or an extended stored
procedure. Also supports the execution of a character string within a
Transact-SQL batch." and I read this (along with the error messages when I
tried it anyway) to mean that TSQL statements are not included which
suprises me as I am sure that I have exec[ute]d 'select ...' commands
before!
Does anybody have any suggestions as to how I can execute the grant
statement within the loop as shown above.
Any help will be gratefully accepted; I would hate to have to set the
permissions manually!"Martin Robins" <martin - robins @. ntlworld dot com> wrote in message
news:ek85i1WlDHA.2436@.TK2MSFTNGP09.phx.gbl...
> I am lazy. I thought that I would get this out in the open from the
outset.
> Now, that said, I will justify it. I like to set up little routines that
do
> all of the things that I keep forgetting to do, such as setting the
> permissions on new stored procedures that I add to a database.
> I have a problem however; I cannot use the 'grant execute on myobject to
> user' with variables.
> Consider the following taken from my database permissions setup script ...
> ...
> while (@.@.fetch_status = 0) begin
> -- Set the owner to dbo if not already ...
> if (@.objectUid <> @.dboUid)
> execute sp_changeobjectowner @.objname=@.objectName,
> @.newowner='dbo'
> -- Grant public access permission.
> grant execute on @.objectName to public
> fetch next from objectNames into @.objectName, @.objectUid
> end
> ...
> This code generates an invalid syntax error on the lise 'grant execute
...'.
> I have also tried creating a variable containing the command with the
> variables expanded and using exec[ute] to execute the command. This also
> fails as exec[ute] "Executes a scalar-valued, user-defined function, a
> system procedure, a user-defined stored procedure, or an extended stored
> procedure. Also supports the execution of a character string within a
> Transact-SQL batch." and I read this (along with the error messages when I
> tried it anyway) to mean that TSQL statements are not included which
> suprises me as I am sure that I have exec[ute]d 'select ...' commands
> before!
> Does anybody have any suggestions as to how I can execute the grant
> statement within the loop as shown above.
> Any help will be gratefully accepted; I would hate to have to set the
> permissions manually!
>
exec('grant execute on ' + @.objectName + ' to public')
It sounds like you're doing this already, so maybe it's just a typo. You
might consider writing your script like this, as it makes troubleshooting
much easier:
set @.sql = 'grant execute on ' + @.objectName + ' to public'
if @.debug = 1 print @.sql
else exec(@.sql)
Add a @.debug parameter to your procedure/script, and you can easily check
that your code is doing what you think it is.
Simon|||Thankyou Simon.
I did not have a typo as such, more a lack of knowledge.
my exec[ute] statement was:
set @.grantStatement = N'grant execute on [' + @.objectName +N'] to
[public]'
exec @.grantStatement
This was generating the error "The name 'grant execute on
[BrowseAddressesByCompany] to [public]' is not a valid identifier.", however
by putting in the brackets as shown in your example that allowed the
statement to execute.
Cheers.
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3f912ea1$1_2@.news.bluewin.ch...
> "Martin Robins" <martin - robins @. ntlworld dot com> wrote in message
> news:ek85i1WlDHA.2436@.TK2MSFTNGP09.phx.gbl...
> > I am lazy. I thought that I would get this out in the open from the
> outset.
> > Now, that said, I will justify it. I like to set up little routines that
> do
> > all of the things that I keep forgetting to do, such as setting the
> > permissions on new stored procedures that I add to a database.
> >
> > I have a problem however; I cannot use the 'grant execute on myobject to
> > user' with variables.
> >
> > Consider the following taken from my database permissions setup script
...
> >
> > ...
> > while (@.@.fetch_status = 0) begin
> >
> > -- Set the owner to dbo if not already ...
> > if (@.objectUid <> @.dboUid)
> > execute sp_changeobjectowner @.objname=@.objectName,
> > @.newowner='dbo'
> >
> > -- Grant public access permission.
> > grant execute on @.objectName to public
> >
> > fetch next from objectNames into @.objectName, @.objectUid
> > end
> > ...
> >
> > This code generates an invalid syntax error on the lise 'grant execute
> ...'.
> >
> > I have also tried creating a variable containing the command with the
> > variables expanded and using exec[ute] to execute the command. This also
> > fails as exec[ute] "Executes a scalar-valued, user-defined function, a
> > system procedure, a user-defined stored procedure, or an extended stored
> > procedure. Also supports the execution of a character string within a
> > Transact-SQL batch." and I read this (along with the error messages when
I
> > tried it anyway) to mean that TSQL statements are not included which
> > suprises me as I am sure that I have exec[ute]d 'select ...' commands
> > before!
> >
> > Does anybody have any suggestions as to how I can execute the grant
> > statement within the loop as shown above.
> >
> > Any help will be gratefully accepted; I would hate to have to set the
> > permissions manually!
> >
> >
> exec('grant execute on ' + @.objectName + ' to public')
> It sounds like you're doing this already, so maybe it's just a typo. You
> might consider writing your script like this, as it makes troubleshooting
> much easier:
> set @.sql = 'grant execute on ' + @.objectName + ' to public'
> if @.debug = 1 print @.sql
> else exec(@.sql)
> Add a @.debug parameter to your procedure/script, and you can easily check
> that your code is doing what you think it is.
> Simon
>

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
>

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?

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 Create/Drop user Table permissions?

HI All,
I have a stored proc that runs and creates a temporary table for collecting
data and then drops it when it is done. My problem is that I can, as DBO,
run this fine but my users can not. How do I allow Create/Drop User tables
from a stored proc? Let me phrase that a different way; what kind of
permissions do I need to set up so that user's can run this stored proc that
creates/drops a temporary table? The stored proc already as the required
EXEC permissions for the user/groups to run it.
Thanks very much,
John.Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Excellent Idea! I completely forgot about that.
Thanks very much :)
By the way, is it necessary to drop temporary tables and or check for their
existence before creating them?
Thanks again ,
John.
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
Hi John,
Your users don't need any special permissions to create temporary tables,
that is if you use real temporary tables, the ones prefixed with a #
character. From your narrative I get the impression that you use permanent
tables as temporary tables, but that is not advisable.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Thanks for the correct syntax; I was drawing a mind blank this morning. Is
it possible to grant the same prev to a group instead of individual users?
John.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:us9TIRa6DHA.3360@.tk2msftngp13.phx.gbl...
Hi,
You should give the below prev. to the normal database user.
grant create table to username
Drop table is not necessory because the owner who create the table can drop
the table.
Thanks
Hari
MCDBA
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> HI All,
> I have a stored proc that runs and creates a temporary table for
collecting
> data and then drops it when it is done. My problem is that I can, as DBO,
> run this fine but my users can not. How do I allow Create/Drop User
tables
> from a stored proc? Let me phrase that a different way; what kind of
> permissions do I need to set up so that user's can run this stored proc
that
> creates/drops a temporary table? The stored proc already as the required
> EXEC permissions for the user/groups to run it.
> Thanks very much,
> John.
>|||Hi John,
Temporary tables are dropped automatically when they go out of scope, which
means that if you create a temporary table in a stored procedure the
temporary table will be dropped when the stored procedure completes. In any
case, temporary tables are dropped when the user diconnects from the
database, and internally temporary tables created by different users have
different names, although they all seem to have the same name to the users,
so multiple users can create the same temporary table at the same time.
--
Jacco Schalkwijk
SQL Server MVP
"John Rugo" <jrugo@.patmedia.net> wrote in message
news:uOWIdea6DHA.2760@.TK2MSFTNGP09.phx.gbl...
> Excellent Idea! I completely forgot about that.
> Thanks very much :)
> By the way, is it necessary to drop temporary tables and or check for
their
> existence before creating them?
> Thanks again ,
> John.
> "Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
> news:OUKZyOa6DHA.3704@.tk2msftngp13.phx.gbl...
> Hi John,
> Your users don't need any special permissions to create temporary tables,
> that is if you use real temporary tables, the ones prefixed with a #
> character. From your narrative I get the impression that you use permanent
> tables as temporary tables, but that is not advisable.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "John Rugo" <jrugo@.patmedia.net> wrote in message
> news:eytDgEa6DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > HI All,
> >
> > I have a stored proc that runs and creates a temporary table for
> collecting
> > data and then drops it when it is done. My problem is that I can, as
DBO,
> > run this fine but my users can not. How do I allow Create/Drop User
> tables
> > from a stored proc? Let me phrase that a different way; what kind of
> > permissions do I need to set up so that user's can run this stored proc
> that
> > creates/drops a temporary table? The stored proc already as the required
> > EXEC permissions for the user/groups to run it.
> >
> > Thanks very much,
> > John.
> >
> >
>
>