468,110 Members | 1,609 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,110 developers. It's quick & easy.

Stored procedure permissions with xp_cmdshell on SQL 6.5

Is there any way to allow a user to use the xp_cmdshell extended
stored procedure without giving that user execute permissions to
xp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as the
dbo) create a stored procedure called sp_send_err:

CREATE PROCEDURE sp_send_err @CompID varchar(20) AS
declare @strCMD varchar(255)
select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + "
""ERROR!""', no_output"
execute (@strCMD)
GO

Now lest say I give "user1" execute permissions on sp_send_err, but no
permissions on xp_cmdshell. When I run sp_send_error I get the
following error:

"EXECUTE permission denied on object xp_cmdshell, database master,
owner dbo".

Why doesn't this work? What else can I do?
Jul 20 '05 #1
1 5112
Micah Gentry (mg*****@ats.bwauto.com) writes:
Is there any way to allow a user to use the xp_cmdshell extended
stored procedure without giving that user execute permissions to
xp_cmdshell in SQL server 6.5? Let me clarify. Lets say I (as the
dbo) create a stored procedure called sp_send_err:

CREATE PROCEDURE sp_send_err @CompID varchar(20) AS
declare @strCMD varchar(255)
select @strCMD = "master.dbo.xp_cmdshell 'net send " + @CompID + "
""ERROR!""', no_output"
execute (@strCMD)
GO

Now lest say I give "user1" execute permissions on sp_send_err, but no
permissions on xp_cmdshell. When I run sp_send_error I get the
following error:

"EXECUTE permission denied on object xp_cmdshell, database master,
owner dbo".

Why doesn't this work? What else can I do?


First, don't use sp_ as the first three letters in the names of your
stored procedures. That prefix is reserved for system procedures, and
SQL Server first looks in the master database for these.

That does not work, because when you use EXEC(str), it is always the
permissions of the actual user that counts.

Then again, there is no reason for use dynamic SQL. This is better:

CREATE PROCEDURE send_err @CompID varchar(20) AS
declare @strCMD varchar(255)
select @strCMD = 'net send " + @CompID + "ERROR!"'
execute master.dbo.xp_cmdshell @strCMD, no_output
GO

It may still not be sufficient though, as ownership chains may cause
trouble. You should probably send_err in the master database, and
grant access to public to this procedure. (If you only want grant
access to certain logins, you would have to add these login to the
master datanase.)

Finally, I see that you are using " as a string delimiter in SQL. In
SQL2000 (I know that you are not there yet, but maybe one day), the
setting QUOTED_IDENTIFIER is ON by default for most client libraries.
With this setting " delimits identifiers and not strings. Thus, it's
better to always use ' as a string delimiter for SQL.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by m3ckon | last post: by
6 posts views Thread by Martin Feuersteiner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.