Does anyone have a script to reset SQL login passwords?

We are doing a consolodation whereby we are pulling 2 servers into one big one. We have a problem with duplicate logins and differing passwords. I want to be able to cursor (or tran?) through a list of logins resetting their passwords to a default.

This will currently run under SQL 2000, but I may want to do it on our 2K5 server at some time - I feel the second part may be more difficult though.

Does anyone have a script lying around in their armoury that will do this?

Thanks in advance

Under SQL 2000, I think I can cursor through master.dbo.sysxlogins and reset the passwords using sp_password, :

sp_password Null, 'Newpassword', 'loginname'

Anyone see a problem with the above?

|||

Hi,

this should be no problem, but keep in mind that in SQL Server 2005 has another table for the logins:

Select * from sys.syslogins

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

In SQL Server 2005, you can issue for each login a statement like:

ALTER LOGIN <login_name> WITH PASSWORD = <default_password>

See http://msdn2.microsoft.com/en-US/library/ms189828.aspx for more information on ALTER LOGIN.

Thanks
Laurentiu

|||

Also, in SQL Server 2005, the catalog for SQL logins is sys.sql_logins:

http://msdn2.microsoft.com/en-us/library/ms174355(SQL.90).aspx

Thanks
Laurentiu

|||It's also possible to reset the password (ie. without knowing the original password) for an individual login using MSSMS (Management Studio). After connecting to the DBSERVER with approrpriate rights to reset a password, navigate to DBSERVER -> Security -> Logins -> AccountNameForWhichToResetPassword. Right-Click -> Properties. Just change the password and confirmation password, then click OK.

Does anyone have a script to reset all logins on a server?

We want to merge 2 SQL servers into one. This means we will have duplicate logins with differing passwords.

We want to reset all the passwords on one server to something memorable. Does anyone have a script that cursor through (or trans - I'm not shy) all the logins in a mster db? I really don't fance going through hundreds of logins changing their passwords by hand!

Looking into it, I think I can cursor through master.dbo.sysxlogins and reset the passwords using sp_password, as such:

sp_password Null, 'Newpassword', 'loginname'

Anyone see a problem with the above?