Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jess
Starting Member
17 Posts |
 Posted - 2007-02-22 : 11:41:50
|
| Hi, I'm having problems using xp_cmdshell.If I log in to Query Analyzer as 'SA' there are no problems, but I need other logins to be able to use the xp_cmdshell function also and they don't have the rights. How do I correct this?I've tried adding a line at the beginning of the code that says:EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0to set it so that you don't have to be a system admin to use the xp_cmdshell function.So my entire example code (to simply create a new directory on the C:\ called 'Test') would be this:EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0EXECUTE master..xp_cmdshell 'md c:\Test'SELECT 'Created Directory C:\Test' as 'Message'EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 1 -- I assume I have to set this back to 1 when I'm done??? So it should create the directory and display the 'Message'.Now I'm able to run this when logged in as a non - 'SA' login, but it doesn't work correctly. It actually DOES create the new directory on the C:\, but instead of going on and displaying the 'Message', it bombs out and gives the following error:[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionCheckForData (CheckforData()).Server: Msg 11, Level 16, State 1, Line 0General network error. Check your network documentation. Connection BrokenAny ideas on either:1: How do I fix that last error or2: Is there a different way to allow users to be able to use the xp_cmdshell function?Please keep in mind that I'm fairly new to this and I'm not the one that set up our network or our server permissions or anything so you may have to talk down to me a little so I understand... I'm no dbo guru or anything just yet! Thanks so much for any help you can offer!!Jess |
|
|
jess
Starting Member
17 Posts |
Posted - 2007-02-22 : 16:17:21
|
| Update:I am able to get the code to work if I set the user as a system administrator in Enterprise Manager and so that will work for what we need.Out of curiosity though, does anyone have any ideas about getting it to function for non system administrators like my previous post mentioned?Thanks,Jess |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-22 : 17:43:38
|
| You need to setup a SQL Server Agent proxy account. It is explained in SQL Server Books Online in the xp_cmdshell topic."When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail."CODO ERGO SUM |
|
|
|
|
|
|
|
|
xp_cmdshell issue