Consequences of changing SA password

  • Let me first say, I'm not a DBA but I've been forced into that role by circumstances at my company. Our DBA quit the company after he didn't get a promotion he thought he deserved, and he didn't tell anyone the SA password to our production instance (and wouldn't later when asked directly, claimed he stored passwords on his iphone which we remotely wiped).

    Fortunately for us, he didn't follow best-practices by removing Builtin\Administrator from the sysadmin role, so we should be able to change it easily. But the question came up as to whether scheduled jobs owned by SA will fail after we change the password. It would seem to me that I should be able to change the SA password and recycle SQL Server, and life would be good.

    Is there something else I need to consider before doing so?

    TIA

    John

  • Ouch!!!!!

    You may have a serious issue with this.

    Changing the password will in some instances not affect anything because it is the security token being passed around the system and not the password. If it is Trusted connections only then again you will be OK to change the password.

    But if you have mixed authentication and (load processes, extracts, automated anything) then you need to be very, very, very, very, careful!!!!!!! I suggest that you run profiler for a good week+ to see what accounts are login before you change the password. I have come across applications that were installed under SA privs and the only way that the SA password could be changed was to reinstall the app. You will also not immediately know whether or not a once a month business critical process requires SA.

    Good luck, and walk softly

  • No jobs etc. should not be affected by changing the SA password. It saddens me your previous guy was called a DBA, this isn't the way a professional should work! ( pay rise or not ).

    You're quite lucky as I remove bulitin admins - however, we maintain DBA permissions with an AD group.

    actually just selecting from sys.sysprocesses should show if users are conencting as SA.

    If you have apps connecting as sa chances are the passsword will be simple or generic so you could run a password compare script to try to find the sa password.

    I'd actually say if an application is connecting as sa and they don't know the password then changing it is fair game!!

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 3 posts - 1 through 3 (of 3 total)

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