Post reply

Changing password of sa account and / or disabling the sa account -> SQL agent jobs

  • September 13, 2017 at 9:34 am

    #349123

    Hi 

    I have some servers where sa account is enabled and seems to be connected internally. I have checked agent jobs and some jobs are owned by the sa account. 

    If I disable the sa account or change the password will these jobs still run? I do not know the current password for the sa account. 

    Thanks in Advance

  • September 13, 2017 at 9:53 am

    #1959280

    Yes,
           if sa is disabled, sa owned jobs will run and sa owned databases will be accessible.  SA is disabled on all our boxes at setup and we have no problems with sa ownership. 
        However, I wouldn't necessarily trust "Random Bod On The Internet", you should test it first to make sure you're happy to put the configuration live.

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

  • September 13, 2017 at 10:17 am

    #1959285

    @ thanks andrew gothard, that is what I thought. thanks for confirming.

  • September 13, 2017 at 11:29 am

    #1959302

    Ownership doesn't require logging in. It's just about what security principal is associated with the job (or DB, or other things that can be owned)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 13, 2017 at 12:08 pm

    #1959309

    Just to add, as far as changing database owners, one thing we noticed when we changed the owner of the databases to 'sa', the previous owner which was removed as the owner also lost their rights to the database and had to be re-added to them.  That may be something you may need to capture, if you change the database owners, prior to changing the owner, so you can apply them back if needed.

  • September 13, 2017 at 12:42 pm

    #1959314

    For me that's one reason to have sa as the DB owner. So that I won't have any cases where logins have more rights than their assigned permissions state.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply