December 11, 2013 at 5:18 am
I Did enable this but still error comes..
We have a SSIS package & we want to execute it via SQL agent JOB..
below error comes
Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 1
The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.
i tried alll option on gole.. but i fail
************************************
Every Dog has a Tail !!!!! :-D
December 11, 2013 at 6:45 am
You must have a step in your SSIS package that's trying to run a command shell. I can't speak to much of anything about SSIS because I don't know much about it other than how to spell it. 😉
In short, the login that's executing the xp_cmdshell procedure need to have permission to do so. It isn't a service, but rather a procedure. There's a lot of debate about how to do this securely and if it should be done at all, and I'm not trying to start a riot here. The following are the steps to securely configure it as outlined by Jeff Moden at http://www.sqlservercentral.com/Forums/Topic1511085-3077-2.aspx. I've seen him present this at a SQL Saturday, but wish I'd taken better notes from his full presentation.
Jeff Moden (11/6/2013)
To summarize...1. You need to have a Windows account setup for the xp_CmdShell proxy. It can and should be just a "standard" user.
2. You need to create the xp_CmdShell proxy.
3. The database needs to be owned by "SA" even if the "SA" user is disabled (AND IT SHOULD BE!!)
4. Create the proc with the WITH EXECUTE AS OWNER phrase.
5. Make sure the proc isn't subject to DOS INJECTION.
6. Grant users the ability to execute the proc. They'll be able to run it but they won't be able to run xp_CmdShell directly. DO NOT EVER LET NON-SA USERS EXECUTE XP_CMDSHELL DIRECTLY!!!
Check out the full thread of the post so you have the full story. You can search around this site for xp_cmdshell if you want to know the full debate.
HTH
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply