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 http://support.microsoft.com/default.aspx?scid=kb;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
begin
fetch next from login_cursor
into @name

--Drop logins
EXEC sp_droplogin @name

end

--Close Cursor

close login_cursor
deallocate login_cursor
go


/*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'

go 
/*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'
go



--Reattach Database

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

Rate

Share

Share

Rate