473,387 Members | 1,492 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

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 5402
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: sylmart7 | last post by:
I am trying to run xp_cmdshell from the Query Analyzer using SQL Server 2000 running on Windows 98. It seems like it should be simple - I'm typing xp_cmdshell 'dir *.exe' in the Query...
1
by: m3ckon | last post by:
Hi, I'm writing a stored procedure to run a dts package and I've successfuly got this working using my sotred proc and the syntax of dtsrun is correct. However, I'm trying to pass a variable...
6
by: Martin Feuersteiner | last post by:
Dear Group I have found that table A had SELECT permissions for 'Public' but not table B. Giving 'Public' SELECT permissions on table B did the trick. HOWEVER, I don't want anyone to be able...
1
by: Brad H McCollum | last post by:
I've looked through many suggestions and partial examples all over this newsgroup and still am not coming up with anything that does specifically what I'm wanting to accomplish. I'm writing a VB...
11
by: BTR | last post by:
I have been working on this particular project for a little over 2 weeks now. This product contains between 700-900 stored procedures to handle just about all you can imagine within the product. I...
3
by: Cesco | last post by:
Hallo to everybody. I have a DTS in SQL Server 2000 and I need to execute it from stored procedure. I know that there are various method fot does this but they doesn't work. The first method that...
3
by: phantom | last post by:
Greetings, I have a sp that dumps text into a textfile but I am having trouble creating the textfile. EXEC master.dbo.xp_cmdShell '\\servername\d$\The File\sub\filename.dat' The directory...
7
by: Terri | last post by:
I have a stored procedure that creates an xml file. It executes a SELECT statement with the FOR XML clause and then writes the xml file using bcp and xp_cmdshell. I am calling this procedure by...
2
by: acw | last post by:
On a SQL Server 2000 db I would like to setup a stored procedure that accesses couple tables and runs the extended stored procedure xp..cmdshell. The goal is to grant users with limited privileges...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.