Chaning SA Password

Question

Chaning SA Password

kanchan_seshadri

Newbie

Points: 3
More actions
July 30, 2009 at 1:01 pm

#134346

I have been tasked to change the SA password on SQL 2005.
I know how to change that but I do not know where all this password is used. Basically I need to know if I change the password what are all the parts of SQL affected, in which places the password is used? And if I change the password do I have to re start the server? I have a lot of linked servers, how will that work.
Thanks
KS

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

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

Animal Magic SSChampion Points: 13964 More actions · Answer 1

Well you have 2 options

1) Change the password - you wont need to restart anything but any already open connections will continue to work until they need to reconnect. see what fails, and then deal with it when it does. its risky but if you have little or no documentation of what users your applications and services use then sometimes its worth doing

2) run profiler and monitor connections using the sa username, work out what all the connections are from and deal with them one by one until no more connections are being made.

Hopefull your developers wont have written much that uses the sa password but i have worked (and currently work) in an environment where sa was the only thing used for connection strings (including all the excel reports/crystal/applications etc etc) - all fun 🙂

Richard M. SSCertifiable Points: 7401 More actions · Answer 2

In theory, the SA account should not be used by any process at all, only for sysadmin purposes..... but, as we all know that is not always the case.

Running the profiler as indicated before is probably the best approach so you can capture and see what is using that account. It's no guarantee if you have external processes that don't run continuously, but definitively a better approach than just changing it.

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

MartinSchmidt Mr or Mrs. 500 Points: 525 More actions · Answer 3

another option could be to sample sys.sysprocesses once every x sec.

not as waterproof as profiler - but with les overhead

Jeff Moden SSC Guru Points: 1004360 More actions · Answer 4

Heh... whatever you do, remember what the old password was so you can reuse it if all hell breaks loose. 😛 Yeah... I know... how obvious can I be? You'd be surprised at the number of folks that miss a simple detail like that.

One of the things you can do from SQL Server is to check the system views in each database to see if there is any code LIKE the password. Of course, that won't help the GUI code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)