Not if you haven't set up a SQL Agent Proxy account. I beg to differ! You may not have set one up manually but carried it over from an upgrade from a prior edition.
From Books Online:
SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell and own SQL Server Agent jobs. The administrators can assign appropriate security permissions to the proxy account to control the ability of these jobs to access resources in the network.
When a SQL Server user executes a command prompt command using xp_cmdshell, the command must execute in the security context of a Windows account. If the SQL Server user is a member of the sysadmin fixed server role, SQL Server executes the command prompt command using the Windows account under which the SQL Server service is running. If the SQL Server user executing xp_cmdshell is not a member of the sysadmin fixed server role, SQL Server executes the command using the Windows account specified as the SQL Server Agent proxy account. If no SQL Server Agent proxy account has been set, the user gets an error. SQL Server Agent jobs also must execute in the security context of a Windows account. If the job is owned by a member of the sysadmin fixed server role, the job executes using the Windows account under which the SQL Server service is running. If the job owner is not in sysadmin, the job executes using the SQL Server Agent proxy account, and an error is raised if no proxy account has been set.
xp_sqlagent_proxy_account sets or retrieves the proxy account for the instance on which it is executed. The SQL Server service for that instance must be running under a Windows administrator account to read or set the SQL Server Agent proxy account.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
Sincerely,
Anthony Thomas, MCDBA, MCSA
-----Original Message-----
From: Uday Shivamurthy [mailto:[Email address protected]
Sent: Saturday, August 28, 2004 6:15 AM
To: Thomas, Anthony
Subject: RE: Permission to run CmdExec job steps
Sorry, I beg to differ. I've created heaps of user accounts on our production sql servers. I just had to add these a/cs to the master db and grant them exec rights on xp_cmd shell game over!!
Bad practice - agreed, but, saying **only** SAs can run this is wrong
Regards,
Uday
-----Original Message-----
From: Thomas, Anthony
Sent: Saturday, August 28, 2004 3:00 AM
To: 'Uday Shivamurthy '; LazyDBA Discussion
Subject: RE: Permission to run CmdExec job steps
Ehhh, wrong. Only SA's can run extended stored procedures.
You have to configure a Windows Account to be used as the SQL Agent proxy account. Then, in the SQL Agent properties, there is check box that disallows anyone but the sysadmins from running command level jobs. If you disable this, you can add the account credentials for the account that will run user jobs when they are not sa's. This is bad practice.
In earlier versions, this was the CmdExec.
Have them set up there job then after inspections and testing, assign the sa as the owner of the job.
Sincerely,
Anthony Thomas, MCDBA, MCSA
-----Original Message-----
From: Uday Shivamurthy [mailto:mssqldba-ezmlmshield-x66856648.[Email address protected]
Sent: Thursday, August 26, 2004 6:55 PM
To: LazyDBA Discussion
Subject: FW: Permission to run CmdExec job steps
Hi Cesar,
You need to add this account as a "user" in the master db.
Then go the "stored procedures" in master db
Find "xp_cmdshell" and double click it
Click on "permissions"
Find your sql server login account and grant him/her "execute" permission
for this particular extended procedure.
Of-course you can do it all using t-sql, but, above method is easy for you.
Regards,
Uday
-----Original Message-----
From: cesaralt
[mailto:mssqldba-ezmlmshield-x89163693.[Email address protected]
Sent: Wednesday, 25 August 2004 9:32 AM
To: LazyDBA Discussion
Subject: Permission to run CmdExec job steps
Hi,
I am trying to run a scheduled DTS Package and I got the following message:
"Non-SysAdmins have been denied permission to run CmdExec job steps. The
step failed."
How Do I assign permissions to users to run DTS job
steps?
Thanks for any help.
Regards,
Cesar Altamirano
---------------------------------------------------------------
EC Red Internet [Email Address Removed] en www.ec-red.com
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
For additional commands, e-mail: mssqldba-[Email address protected]
***********************************************************************
NOTICE: This electronic mail message and any attached files are
confidential. The information is exclusively for the use of the
individual or entity intended as the recipient. If you are not the
intended recipient, any use, copying, printing, reviewing, retention,
disclosure, distribution or forwarding of the message or any attached
file is not authorized and is strictly prohibited. If you have received
this electronic mail message in error, please advise the sender by reply
electronic mail immediately and permanently delete the original
transmission, any attachments and any copies of this message from your
computer system.
***********************************************************************
MS Sql Server LazyDBA home page