If you’re creating a job process under the SQL Server Agent and you’re expecting to run an OS based command from xp_cmdshell you may run into an error where the SQL Service Account doesn’t have the following 3 rights.
1. The right to back up files and directories.
2. The right to manage auditing and the security log.
3. The right to debug programs.
In most cases these rights are automatically assumed if your account has Administrator rights in the OS, and you’re logged on directly but instead of using an SQL process to run the CMD command; you’re simply using Command Prompt, and you’ve already explicitly opened it using ‘Run as Administrator’.
In security architecture these privileges are knows as the following:
These are fairly easy to add if you’ve got rights to the local security policies, and here’s how to add them.
Go to Startà Run, and run secpol.msc
Expand ‘Local Policies’, and select ‘User Rights Assignments’.
You’ll need to click each one of these and ensure the SQL Service Accounts have been added to them. If you’re creating the Agent Jobs to run under another account that isn’t a service account then you’ll need to add it directly as well.
Categories: SQL Stuff