Differential backup Job executed as 'SA' login Name internally as per the SQL Profiler trace

The SQL Agent may run under a given users Windows credentials, but when you create a new job to run a maintenance plan, you can choose the owner. It is reasonable to choose SA or create a specific user for them to run under.

Hi Lee,
   
 Just want to be sure, you are talking about the owner details given in the Differential Job->properties->owner right? yes there it is mentioned as 'SA'. I have few questions on this?

1)will there be any impact if i change the owner of the Job to the same as the service account on which the SQL agent is running?

2)After i change the owner to other name(preferably the service account of sql agent), will it create any issue in future if the SA login is disabled?

thanks
Deepak

I'd be careful about disabling the sa user. Sometimes it's worthwhile just giving it a decent password and making sure nothing uses it.

Can you be sure nothing relies on the sa account?

If you change the owner, it won't make any difference as long as the owner has the required permissions and security settings to allow the job to run, and access to the right databases.

1)will there be any impact if i change the owner of the Job to the same as the service account on which the SQL agent is running?

Depends. What kind of permission the SQL Agent account has? It has access and it's able to create files in the backup location?

2)After i change the owner to other name(preferably the service account of sql agent), will it create any issue in future if the SA login is disabled?

Why do you need to disable the SA account? Do you have any other user with similar permission?
Also if you don't have any other SQL Server login then you can change the SQL Server authentication to Windows Authentication so SA won't be able to login anymore even if it's enabled.

Hi Virto,
 
  1)What kind of permission the SQL Agent account has? It has access and it's able to create files in the backup location?
            Yes the SQL server Agent account has all the required permissions.

2)Why do you need to disable the SA account?
    There was a debate on this as the SA account details were shared with all the team folks which shouldn't have happened. Also client and security team is much concerned about the SA logins as its a big threat for the hackers(as per their views).

thanks
Deepak

Yes the SQL server Agent account has all the required permissions.

Then you should not have any issue by changing the job owner.

Also client and security team is much concerned about the SA logins as its a big threat for the hackers(as per their views).

Change the password to a strong one or permit only Windows Authentication to the SQL Server instance.

Deepak, any news on this issue?

Hi Vitor\Lee,
      I have been questioned by the client on few columns of Profiler trace data.

Why did the sessionLoginName of profiler trace shows as "service account" on which the sql agent is running is different from the LoginName column which shows 'SA'.

Could you also explain me on which account permissions the SQL agent Job is actually executed? SA or service account.

Your help would be much appreciated!

Thanks
Deepak

Hi Vitor,

   Thank you for your reply, Yes currently the Job owner is 'SA'.

Could you explain me, what would be the best practice to change the job owner from 'SA' to ?

Regards
Deepak

Deepak, please let us know if you still need more help with this question.

Now my question is "Is 'SA' should be the database owner for all the system databases? Is it a recommended practise or something else to consider ?

Yes if you have SQL Server Mixed Authentication enabled.

Have done the deep analysis based on few artifacts and suggestions by Vitor