SQL Server 2000 advice

[Nowell]

Hi,

i have just started a new job as a DBA, the company has survived a while without a DBA but this means too many people know the sa passwords.

I would like to change the sa passwords for each SQL server,

how do i go about adoing this?

Will doing this have any detrmental effects on back up jobs etc?

Cheers

Nowell

 

[nigelrivett]

Anything using sa will fail - a good thing as you can go round changing the users but it will be painful for a time.

CHeck as much as you can before doing it then go for it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[Nowell]

Hi,

What exactly do you mean by everything using sa will fail?

 

[pweegar]

What was meant was, that anything using the sa account must be changed or whatever uses the sa account will no longer work.

Take a look at BOL and do a search for either sa or passwords. You can use the sp_password in T-SQL to change passwords. Do so at your own risk.

 

[Nowell]

Hi,

Sorry I am not quite understanding,
how do you mean everything will need to be changed?
How does it need to be changed?

Why wont things run if the password has changed? Surely it will still be using the sa account and there is no mention of a password when setting up a job.

 

[ecobb]

Everything that has been set up to use sa as the user account will no longer work if you change the password. Whenever you tell something to run as sa, you have to specify the username and password, so if you change the password everything that's using sa now has the wrong password. If you set up Enterprise Manager to use the sa account, you will have to change the password in the connection settings before SQL Server will allow Enterprise Manager to connect. If you have scheduled jobs that are running using the sa account, you will have to change the password on every job because it will be using the old password.

Hope This Helps!

Ecobb

"Alright Brain, you don't like me, and I don't like you. But lets just do this, and I can get back to killing you with beer." - Homer Simpson

 

[Luvsql]

I would suggest creating a new sql login and assign it to a fixed db role such as db_backupoperator. Change all the backups first and make sure they are running properly. Then, change the sa password on each server and test.

The sa user shouldn't really be assigned to anything.

 

[Nowell]

Ok, thanks chaps.

How do I actually go about changing the password?

 

[lisali]

You can change the a login ID's password via EM. Go to Security -> Logins, right click on the 'sa' login ID, select 'Properties'...