SQL Server Performance

Is setting sa as the owner of a sql server job a risk?

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Aug 3, 2013.

  1. Trev256b Member

    I can't find anywhere online to confirm if setting the job owner to 'sa' is a risk. Some DBAs merely say it is not a good idea to give elevated permissions. Many DBAs set the job owner to 'sa' to avoid problems with individual user/DBA accounts being owners.

    1) Is it perfectly ok to set the job owner as 'sa'?
    2) If not what are the risks?
    3) If another account should be used, is a domain account with minimum permissions best practise?

    I can see numerous problems with using a domain account as a job owner: such as working out the exact permissions needed and moving jobs to another domain.
  2. davidfarr Member

    The sysadmin role (of which 'sa' is such a user) is the only role that has unrestricted access to all Agent jobs. Non-sysadmins would need to be added to the MSDB database roles such as SQLAgentUserRole, SQLAgentReaderRole or SQLAgentOperatorRole in order to access an Agent job. Even with these roles, a non-sysadmin can only edit their own jobs (jobs owned by that user).

    Therefore; If all Agent jobs are owned by 'sa', then only a sysadmin can perform changes and maintenance on them. If that's OK (in terms of the DBA's workload and responsibility) then there is no harm in having them all owned by 'sa'.

    The potential danger is this;
    A job owned by 'sa' will execute with 'sa' permissions, and so any serious syntax or object reference error would not be blocked by permissions. If the DBA has an 'accidental' command in the job to drop a critical database object or delete valuable data, then there will be no permissions in place to prevent that.
    So the question might be "Do you trust yourself ?". As DBA; if you are quite sure to verify and check all your jobs before scheduling the execution and if you do not suspect errors in your jobs, then 'sa' ownership is fine, in the sense that it is an acceptable risk.
    Trev256b likes this.
  3. Trev256b Member

    Thanks David. It appears that the DBAs that are merely worried about elevated permissions need only worry about themselves! :)

    As always, it is a question of what approach you want to take depending on what work you want to do (eg. manage separate accounts or test/verify the jobs).

    Your advice has now allowed to me to confidently choose an approach knowing the factors and consequences involved. Thanks again.

Share This Page