SQL Server Wave

While working on sql server consolidation, I was required to transfer all logins to the new sql server instance. I was worried about not knowing the passwords for a few Windows domain logins. I then realized that passwords are not required when transfering them.

All one needs to do is run the scripts available at the following link:

http://support.microsoft.com/kb/918992

The script will generate the script for creating all logins under the sql instance. For example:

– Login: Test
CREATE LOGIN [Test_Login] WITH PASSWORD = 0x01006B867DAEEEBC1207A2232FA7B155A7F7670543341FFF012F HASHED, SID = 0x0393B11B9324C04F930A7F951F05EFFF, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

As you can see in the above example, the password is included albeit encrypted.

The good thing is that you are not required to know the password. The bad thing is that you will end up with a new login having a different sid, therefore not matching with the user of the same name in the database. In that case, all you need to do is to run the following script (courtesy of sqlservercentral.com) to find and remap them:

/**************************ORPHANED USERS****************/
/*** To be run against each db ***/

DECLARE @UserName nvarchar(255)

DECLARE Cursor_OrphanedUser cursor for

SELECT NAME FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0×01)
and suser_sname(sid) is NOT null ORDER BY name

OPEN Cursor_OrphanedUser

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

WHILE (@@fetch_status = 0)

BEGIN

PRINT @UserName + ‘ Synchronization of Logins in Progress’

EXEC sp_change_users_login ‘Update_one’, @UserName, @UserName

FETCH NEXT FROM Cursor_OrphanedUser INTO @UserName

END

CLOSE Cursor_OrphanedUser

DEALLOCATE Cursor_OrphanedUser

/************************END ORPHANED USERS********************/

Hope you will find this useful.