SQL Server agent jobs without sysadmin

Question

Hi there,

I'd like to know how can we create a profile in SQL server 2008 that can run SQL Server agent jobs without the sysadmin privilege. We were unsuccessful using the roles in the msdb to do this.

Thanks!

Answer 1

Hello,

To configure a user to create or execute Microsoft SQL Server Agent jobs, you must first add an existing SQL Server login or msdb role to one of the following SQL Server Agent fixed database roles in the msdb database: SQLAgentUserRole, SQLAgentReaderRole, or SQLAgentOperatorRole

See this link

http://technet.microsoft.com/en-us/library/ms187901.aspx

Then using Sp_addrolemember you need to provide following roles to the login

• SQLAgentUserRole

• SQLAgentReaderRole

• SQLAgentOperatorRole

  To read more about the roles please see below link
• http://technet.microsoft.com/en-us/library/ms190926.aspx

To read about SP_addrolemember see below

http://technet.microsoft.com/en-us/library/ms187750.aspx

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

Answer 2

In SQL Server Management Studio

1. Create an credential in security node of the server, from an existing account (from Active directory or local account).

2. In SQL Agent proxies, create a new proxy that map the credential, note that every proxy its used in a type of proxy, so every type have a correspondant of the STEP type of the JOB.

3. in step of the job choose you proxy

http://technet.microsoft.com/en-us/library/ms175834.aspx