Moving users to and from SQL Servers

OK, I have been moving users around a lot lately. Posting this should save me some time on having it done in the future. I know there several articles out there on how to do it.. I am posting this, for ease of use on my part and maybe others.. now we don't have to swim through all the MSFT documents to get to the "nitty-gritty" ( I loved Nacho Libre.. "Chancho! I need to borrow your sweats")

You would use this information if you just restored a database to a new server and then realized "What the crap! These users restored with the database, but their logins are not here! That's STUPID!!
Jeez, chill out.. Freak!
No fear, follow the steps below for some serious logins transferring fun!

Sequential time line here:
1. Restore database to new server
2. then run these below steps.. DON'T try and mess with the logins in the master database .. don't try and manually add them in EM or Query Analyzer That's DUMB.. they have their own internal Id assigned them in the DB, these scripts bring them down as tough they are the same.


Alright, the following script is run on the master db. and will create the SPROC that will spit out all the usernames with their current Passwords! THIS APPLIES TO TRANSFERRING LOGINS FOR 2000 AND 7.0
1. Run this on the Source server ( the server you want to transfer the Users FROM)

----- Begin Script, Create sp_help_revlogin procedure -----

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar(256) OUTPUT
AS
DECLARE @charvalue varchar(256)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length) 
BEGIN  
DECLARE @tempint int  
DECLARE @firstint int  
DECLARE @secondint int  
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))  
SELECT @firstint = FLOOR(@tempint/16)  
SELECT @secondint = @tempint - (@firstint*16)  
SELECT @charvalue = @charvalue +    SUBSTRING(@hexstring, @firstint+1, 1) +    
SUBSTRING(@hexstring, @secondint+1, 1)  
SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue 
go

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL  
DROP PROCEDURE sp_help_revlogin 
GO 
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name    sysname DECLARE @xstatus int DECLARE @binpwd  varbinary (256) DECLARE @txtpwd  sysname DECLARE @tmpstr  varchar (256) DECLARE @SID_varbinary varbinary(85) DECLARE @SID_string varchar(256)  IF (@login_name IS NULL)  DECLARE login_curs CURSOR FOR    SELECT sid, name, xstatus, password FROM master..sysxlogins    WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@xstatus & 1) = 1
BEGIN -- NT login is denied access
  SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
  PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
  SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
  PRINT @tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
  EXEC sp_hexadecimal @binpwd, @txtpwd OUT
  IF (@xstatus & 2048) = 2048
    SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
  ELSE
    SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
  PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
END
ELSE BEGIN
  -- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
  SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
    + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
IF (@xstatus & 2048) = 2048
  -- login upgraded from 6.5
  SET @tmpstr = @tmpstr + '''skip_encryption_old'''
ELSE
  SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
----- End Script -----
----- End Script -----

OK, now step 2

2. Run this on the SOURCE server

EXEC master..sp_help_revlogin

3. Copy the results from step 2 and run them on the DESTINATION Server. (The server you want the new users to be transfered to).... That's it.. the new logins/users are there and their passwords are the same from the previous server. Now, you might need to run some default database changes. I think this script they default to the master db. No fear, this is easily remedied with a little script, and a cursor.. Bing! Your done..