Technical Article

Updating Changed Passwords for warm Servers


One of the challenges weve faced is changing passwords in a warm server to reflect changes made to the live server.  The initial transfer of users and passwords was performed by the   sp_help_revlogin   script provided by Microsoft at;en-us;246133.  The difficulty with this script was once the users are in place, you cant sync changed passwords.  Although Im certain there must be script or tool that does this I was unable to locate it.  As a fix I wrote the following script for updating passwords between to SQL 7 DBs. 

/*Detach db, this allows you to stop errors caused by orphan users in DBs*/ 
EXEC sp_detach_db 'test', 'true'

--Declare cursor
Declare login_cursor cursor for

/*Select users you want to transfer.  I've out NT accounts (name NOT LIKE '%\%')
and sa account, trying to drop will cause an error */
SELECT   name
FROM         sysxlogins
WHERE     (name IS NOT NULL) AND (name NOT LIKE '%\%') AND (name NOT IN ('sa'))

--Set variable for name field

Declare @name  varchar(20)

--Open Cursor

open  login_cursor

--Loop through records

while @@fetch_status = 0
fetch next from login_cursor
into @name

--Drop logins
EXEC sp_droplogin @name


--Close Cursor

close login_cursor
deallocate login_cursor

/*Create statement to extract users from DB, pump them to C:\login.sql
The sp_help_revlogin is a common script provided by microsoft to transfer users to a new database.
this can be found at their web site. */
xp_cmdshell 'ISQL -USA -Ppassword -SServer1 -Q"EXEC sp_help_revlogin"  > C:\LOGIN.SQL'

/*use the this statement to execute the previous created statement.  This creates a log file to view results */
xp_cmdshell 'isql  -Usa -Ppassword -SServer2 -i C:\LOGIN.SQL -o C:\Login.log'

--Reattach Database

EXEC sp_attach_db @dbname = test, 
   @filename1 = 'C:\test_Data.MDF', 
   @filename2 = 'C:\test_Log.LDF'


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating