November 22, 2006 at 12:10 pm
Need to change sa password periodically for 100 servers. Any advice or suggestions? Any automation tool to minimize DBA work?
I raise the question here and see how the other DBAs do?
November 23, 2006 at 3:30 pm
If all the servers are in the same domain and your domain account are sql server system admin you would do something like this:
Make a bat file where the first line look like this
set sqlquery="sp_password 'oldpassword', 'newpassword', 'sa'"
And then make 1 line for eachh server
osql -S [servername] -E -d master -Q %sqlquery%
/morten
November 27, 2006 at 9:53 am
Morten's got it right, a big old batch file is needed to do the actual work. But take it a step further, put your server names in a table and write a query to write the batch file. This will ensure that all your servers in the list get updated and is easy to maintain. Its also easy to do other, similar tasks, to all your servers.
Don't forget to review your results to make sure all the servers got the task completed successfully. I usually redirect the output to a log file for easy review.
November 27, 2006 at 10:17 am
Thank you Bill and Morten for your suggestion.
I am wondering whether this approach is good for sa password as the password is plain text in a batch file, plus extra maintenance work for the batch to run against all the servers.
In addition, changing sa password may also affect the existing process (DTS pkg, replication, etc). Some work has to be done to reflect the change.
Is there any secure/reliable tool available to do this? Or efficient way to do it?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply