run a job as a domain admin login

Question

hello,

• I have a stored procedure that contains a number of OPENROWSET that reads from many databases on servers other than the server i'm executing the SP on.
• the SP runs fine and brings the required data if i run it using a domain admin account. this domain admin account is a sysadmin on all of the sql servers that the SP reads from.
• i've created a job to runs this SP, and i've selected "Transact-SQL script (T-SQL)" as the type of the only step in this job, but in the General tab of the Job Step Properties window i couldn't find anything. in the Advanced tab i can select just Users and none of them is that domain admin. if i select any other user, i'll get access dined error when i try to run the job .. which is logical.

some threads and other forums noted that a credentials and proxy have to be created ... if this is the solution please i need the detailed steps and the minimum security permissions required to run this job. if this is not the solution or there is a simpler one, please mention it.

thanks in advance

Answer 1

Hi

Definitively you have to create a credential and a proxy. see below link to see how to do it

http://sweetsql.blogspot.com/2010/03/credentials-proxies-and-ssis-sql-agent_02.html

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 2

Hi Butmah,

For a Transact-SQL job step, by default it runs under the security context of the job owner and if the owner of the job is a member of the sysadmin fixed server role, it runs under the SQL Server Agent service account. Specifying the "Run as User" option for the Transact-SQL job step in case you have several job steps and you do not want to change the job owner so as not to impact other job steps execution. You can go to the Advanced options of the job step, and input an user to the "Run as User" field. Ensure you are a sysadmin to edit this option.

For creating credentials and proxies for domain user, here are some references:

Creating SQL Server Agent Proxies: http://msdn.microsoft.com/en-us/library/ms189064.aspx

How to: Create a Credential (SQL Server Management Studio): http://msdn.microsoft.com/en-us/library/ms190703.aspx

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Not working ... let me tell you what did i do exactly so you maybe find where did i miss:

1. I've created a new credential, and the Identity was the domain admin account (the password and the confirmation are the password of the domain admin account).
2. I've created a new proxy: with Credential name is the newly created credentials (the one I created in step 1). i didn't check any check box of the "Active to the following subsystems" because none of them applicable to what i want, I just want to run a T_SQL query that contains OPENROWSET to read from another SQL server instances on other servers.
3. still in creating proxy, i went to the Principals tab to add the domain admin account there, but there was written "Members of the sysadmin role have automatic access; they do not need to be added", even so, i've added it.
4. then I went back to the job trying to find the proxy i've created, but i couldn't find it in the Owner window nor in "Run as user" under the Advanced tab of the only step i have 

what should i do more ?

Answer 4

Butmah,

I remember that Job steps that execute Transact-SQL do not use SQL Server Agent proxies. So, to solve this issue, we have these solutions:

1. The first method to resolve this issue is to change the job owner to a login who has enough permission to execute the T-SQL script but the job owner should not be a sysadmin.
2. The second method is specifying the "Run as User" option for the Transact-SQL job step in case you have several job steps and you do not want to change the job owner so as not to impact other job steps execution. You can go to the Advanced options of the job step, and input an user to the "Run as User" field. Ensure you are a sysadmin to edit this option.
3. The third method is that you can add an EXECUTE AS statement ahead of your T-SQL script.

The direct way is to change your domain account to job owner and grant relevant permissions to it. (The domain account cannot be the sysadmin)

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Thanks Iric,

I've tried the third method and it worked