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?

Does any one know how to reset sa Account?

I'm a programm designer, when using SQL Express Server,
one of my codesigner accidently changed the name of sysadmin account "sa" into somthing else, even disabled the login of this account.

since that's the only one account we can login, the database is now closed to any further operation above basic level.

looking for any possiable means to regain the control of database.

like how to reset "sa" account or reset the "master" database.

we sure make a "security database now" *_*

find a way to reset database using database under "Template Data" folder. this is however not suggested, while lost all the data.

I am still looking for a way to re establish the "sa" account

|||

The database should still support Windows Authentication. Login locally as an Administrator and connect to the database using Windows Authentication. Then reset the sa password.

If you properly secured the database BUILTIN\Administrators has been removed from server admins and this will not work. Additionally, you should not be logging in as sa to begin with. Create other accounts with the appropriate permissions and use them.

|||

well thanks for your advice, it works this time.

however I tried this way before, it did not work, because even permissions for windows authentication to create higher level security account were suspended. with BUILTIN\Administrators still in exist. but it works now.

thanks again.