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 |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2011-07-20 : 15:57:53
|
| Hi,I'm trying to execute a bcp statement, which works when executed by itself from the command prompt, from a stored procedure, which wraps the bcp command and calls it using 'exec master.dbo.xp_cmdshell'.I get the following error message:Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.What does the error message mean?Why is it referring to the hidden system database?From this link 'http://msdn.microsoft.com/en-us/library/ms175046.aspx' it says and I quote:"The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account". To me it means that as long as the user under which the sql server service is running has the permissions to execute 'xp_cmdshell', it should work. Is my understanding correct or are there other possible problems?Thank you! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-07-20 : 16:06:43
|
| I had typed this prior to Tara's response but I'll post anyway:The quote from you link refers to whatever process xp_cmdshell is executing - in your case a bcp command. However, the error you are getting is in calling xp_cmdshell at all.you can confirm that by trying:exec xp_cmdshell 'dir c:'you would need both permission to exec xp_cmdshell as well as read access to the C: drive for that to work. Permissions for executing xp_cmdshell are disabled all users by default with good reason. It is too powerful.To be able to do that you need to make sure the server is configured to allow xp_cmdshell (sp_configure) and the user needs to have EXEC granted for xp_cmdshell. Be thoughtfull with this as anyone with access to your DB and permission to run xp_cmdshell may have complete access to your server and possibly your network.Be One with the OptimizerTG |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-20 : 16:23:59
|
| Just to ammend TG's remarks...xp_cmdshell is always executed in the context of the SQL Server service account.Thus, you're giving any user with permission to it an awful lot of authority. If your service account is local admin, then any user who has access to xp_cmdshell is too.Also, you wouldn't need permissions on the C: drive in the example. The service account would. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-07-20 : 16:40:00
|
Same here.Just to amend Russell's amendment... If the user is not a member of sysadmin server role then xp_cmdshell will run under the xp_cmdshell_proxy_account. If that account has not been established then the call to xp_cmdshell will fail.But establishing that account is one way to limit the scope of xp_cmdshell.And Russell is probably right about permission to C: drive but I have this nagging suspicion that if proxy account thing is set up and the user is not in the sysadmin role then both the account AND the user launching the process would need access to the C: drive. (least privileged account)Be One with the OptimizerTG |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-20 : 17:46:37
|
 |
|
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2011-07-20 : 22:19:31
|
| Thanks for all the replies and useful info! |
|
|
|
|
|
|
|
|
xp_cmdshell execution problem