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 
