4

I am using SQL Server 2008. I am confused about which account will be used when a SQL Server agent job runs. My confusions are,

  1. SQL Server agent as a Windows Service which we could control from Windows Service Management Console, from there we could set the account to run SQL Server Agent (LocalSystem in my computer);
  2. Could I set SQL Server agent job level account to run on?
  3. Could I set in each step which account SQL Server agent job step will run on?

I have above confusions because 3 different account systems may be used and my concern is what is the actual account each step will run on, and I want to avoid any permisson issues (i.e. I want to make sure the account have enough permission.). Any comments or advice? Appreciate anyone could clarify the 3 levels of accounts, which makes me very confused.

thanks in advance, George

George2
  • 44,761
  • 110
  • 317
  • 455

2 Answers2

5

I would typically run the SQL Server Agent jobs under the same account as your app accesses the database.

If that account is too limited in its permissions (which might be a good thing!), I would create a single account for that app and all its SQL jobs (if that's possible) and run all SQL jobs under that account.

You could potentially run each step under a different account, but I wouldn't use that in general (it just makes it really hard to know and understand what is run under which account). Only use it if you have to run a particularly sensitive step that needs a bunch of extra permissions and those permissions are only available to a particular system account or something.

The account under which the SQL Server Agent windows service runs really doesn't have an impact on what your job steps will be run under.

So it boils down to really just two accounts:

  • one account is needed to run the SQL Server Agent Windows service - this is a Windows account on your machine / server which needs to have enough permissions to run the service, start and stop it - either use LocalSystem, Network Service, or whatever other Windows account you have to run services with

  • The other account would be the account to run your SQL Server Agent steps under - that's typically a SQL Server account (which could be based on a Windows account), and it needs enough privileges inside SQL Server to do its job, e.g. it needs access to the database objects and all. I would strive to have just one account for each app that runs the SQL Server jobs - makes life a whole lot easier!

Marc

PS: To set the user to run a step under, you need to use the "Advanced" page on the Job step property dialog and select the user from a popup window:

alt text

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks Marc, in SQL Server Agent Job configuration we could assign Owner, it is just for description purpose when dump event log or it is the actual account which SQL Server agent job will run on? – George2 Aug 01 '09 at 11:25
  • Hi Marc, I like your advice of using the single account. Then should I need to configure the SQL Server agent Windows Service account? Or configure somewhere else? I think we'd better not set the Windows Service account since once set, all jobs belong to the current instance will run under the same account. – George2 Aug 01 '09 at 11:28
  • 1
    George2: the owner in SQL Server is typically the account which can set permissions on the object, e.g. give or take away rights and such. I'm not sure if the owner would be used as the account to run the step under if you don't specify another one specifically. – marc_s Aug 01 '09 at 11:29
  • Thanks Marc, the final confusion point for me is, I want to follow your points to use single account for the specific SQL Server job (and all job steps for the job), and appreciate if you could review whehter my operations are correct, my steps are -- I should set the Owner for the SQL Server job, no need to use Advanced settings for each job step (then each job step will automatically use Owner account for the containing job), and also no need to set the account which SQL Server Agent Windows Service runs? And the Owner for Job is the account which all steps actually runs on? – George2 Aug 01 '09 at 11:40
  • And I just need to make sure the Owner account have enough permission for all steps? – George2 Aug 01 '09 at 11:40
  • 1
    I can't say for sure - I can't seem to find any definitive answers on that one, really. Since my Jobs typically select and update stuff in the database, I am lead to assume that the "Owner" account will be used by default, unless you specify some other account on a given step, yes – marc_s Aug 01 '09 at 11:48
  • Thanks Marc, I have marked your reply as answered. I have a related question here, appreciate if you could help. http://stackoverflow.com/questions/1216584/create-sql-server-job-automatically – George2 Aug 01 '09 at 13:31
  • Here is another related question. :-) http://stackoverflow.com/questions/1216551/how-to-start-sql-server-agent-jobs-automatically – George2 Aug 01 '09 at 13:32
4

You can create Credentials in SQL Server (use Mgt Studio, under Security). Then create a Proxy in SQL Agent to use those credentials, telling it what kind of job steps can be used by the proxy. Then you get the choice to use that Proxy in the job step itself.

So... I make accounts for various SSIS packages to run under, so that I can keep the SQL Agent Service Account low privilege, and use a proxied credential with slightly higher privilege (not admin though, just enough permission to connect to other systems, including the File System).

Rob

Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • Thanks Rob - I was having trouble and this seemed to work perfectly for me. I am running some powershell scripts and needed elevated permissions for some of the steps. nice. – aSkywalker Dec 04 '09 at 18:58