Tips and Tricks for:  SQL Server

The Problem:   xp_sql_agent_proxy_account returned the message "Access Denied"

The extended stored procedure xp_sql_agent_proxy_account returned the message "Access Denied" when doing either a 'GET' or 'SET' operation.

Solution: Add service account to "Administrators" group.

The SQL Server service was running as a service account that did not belong to the "Administrators" group.  It was a domain account that was a only member of "users".  Once the <mydomain>\SQL2KSVC account was made a member of administrators and the SQL Server stopped and restarted, the xp_sql_agent_proxy_account call worked.

Generally, the service accounts for SQL Server and SQL Server Agent (sometimes the same account) should be domain users.  They should be not be domain or local administrators.  You may want to remove the membership in the "administrators" group, after the new account has been tested.  The privilege is needed to change the account, not to run a command with xp_cmd_shell.