locked
Login name logged as SA when running SQL Agent Jobs RRS feed

  • Question

  • Hello,

      When we tried executing SQL agent Job the login name was logged as 'SA' in the profiler trace even after we have made the job step  "Run as" different user which has the system admin privileges and disabled the 'SA' login.

    could you help me out with the following questions?

    1)why the job steps are still executed as 'SA' under Login Name column?

    2)Is it a good practise to change the dbowner for the msdb database from SA to different user who has sysadmin privileges?If it's not recommended kindly provide me the reason for the same?

    thanks

    MohanDakshin 

    Thursday, October 6, 2016 8:28 AM

Answers

  • In case of security many companies just disable SA account  and create some login in sysadmin server role, but even though you disabled the SA account you still may have a database as SA owner..  Shortly , do no change SA from the msdb owner

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 6, 2016 8:36 AM
    Answerer
  • When we tried executing SQL agent Job the login name was logged as 'SA' in the profiler trace even after we have made the job step  "Run as" different user which has the system admin privileges and disabled the 'SA' login.

    >>what about the job owner & you are talking about the job step as "RUN as" different user.
    note that even default trace kicks by sa.

    could you help me out with the following questions?

    1)why the job steps are still executed as 'SA' under Login Name column?

    Note- renaming nor disabling the SA account will not cause internal processes to stop/abort still it works but only you could see login fails when connectiong to the SQL server if anyone trying to use.
    even the job will works.

    might be some of the jobs still using sa nor pointing to other databases which uses sa or yor internal logic of your activity.

    2)Is it a good practise to change the dbowner for the msdb database from SA to different user who has sysadmin privileges?If it's not recommended kindly provide me the reason for the same?

    >Leave as it is,dont know where else it breaks specially MSDB objects,sp's ,packages or any & even not sure on the SP upgrade or migration may affect.

    Regards, S_NO "_"

    Thursday, October 6, 2016 2:14 PM

All replies

  • In case of security many companies just disable SA account  and create some login in sysadmin server role, but even though you disabled the SA account you still may have a database as SA owner..  Shortly , do no change SA from the msdb owner

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, October 6, 2016 8:36 AM
    Answerer
  • When we tried executing SQL agent Job the login name was logged as 'SA' in the profiler trace even after we have made the job step  "Run as" different user which has the system admin privileges and disabled the 'SA' login.

    >>what about the job owner & you are talking about the job step as "RUN as" different user.
    note that even default trace kicks by sa.

    could you help me out with the following questions?

    1)why the job steps are still executed as 'SA' under Login Name column?

    Note- renaming nor disabling the SA account will not cause internal processes to stop/abort still it works but only you could see login fails when connectiong to the SQL server if anyone trying to use.
    even the job will works.

    might be some of the jobs still using sa nor pointing to other databases which uses sa or yor internal logic of your activity.

    2)Is it a good practise to change the dbowner for the msdb database from SA to different user who has sysadmin privileges?If it's not recommended kindly provide me the reason for the same?

    >Leave as it is,dont know where else it breaks specially MSDB objects,sp's ,packages or any & even not sure on the SP upgrade or migration may affect.

    Regards, S_NO "_"

    Thursday, October 6, 2016 2:14 PM