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 a Job execute MUCH faster??

Hi,

Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.

However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.

Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.

I'll appreciate comments. ThanksIt's actually a little simpler than that. The same exact set of processes are involved in running your package(-s) interactively or as a scheduled job. The difference lies in the fact that, while being scheduled, the package runs on the server, and any and all output streams are within the "server reach" (in other words nothing leaves the box), running the same package interactively causes every output flag (such as the number of rows processed so far) to be sent to the client.|||Thanks for the reply :)