How to transfer logins and passwords between instances of SQL Server - Part 1
(if sorce server is sql 7 or 2000)

How to transfer logins and passwords between instances of SQL Server - Part 2(If sorce server is sql 2005 , sql2008  and sql2012)

A complete resolution to transfer logins and passwords between different versions of SQL Server

To do this, use one of the following methods.
Notes

• The scripts in the following methods create two stored procedures that are named the sp_hexadecimal stored procedure and the sp_help_revlogin stored procedure in your master database.
• The scripts are dependent on SQL Server system tables. The structure of these tables may change between versions of SQL Server. Selecting directly from system tables is discouraged.
• Review the remarks at the end of this article for important information about the steps in the methods.
• Method 2 assigns logins to roles.

Method 1

This method applies to the following scenarios:

• You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
• You transfer logins and passwords between servers that are running SQL Server 2000.

To transfer logins and passwords between different versions of SQL Server, follow these steps:

1. Run the following script on the source SQL Server. Continue to step 2 when you finish creating the sp_help_revloginstored procedure.
   
   

   ----- 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 -----
   
   

2. After you create the sp_help_revlogin stored procedure, run the sp_help_revlogin procedure from Query Analyzer on the source server. The sp_help_revlogin stored procedure can be used on both SQL Server 7.0 and SQL Server 2000. The output of the sp_help_revlogin stored procedure is login scripts that create logins with the original SID and password. Save the output, and then paste and run it in Query Analyzer on the destination SQL Server. For example:
   
   

   EXEC master..sp_help_revlogin
   

Method 2

This method applies to the following scenarios:

• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.
• You assign logins to roles.

To transfer logins and passwords between different versions of SQL Server and then assign logins to roles, follow these steps:

1. Run the following script on the source SQL Server.
   
   

   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_2000_to_2005') IS NOT NULL 
   DROP PROCEDURE sp_help_revlogin_2000_to_2005 
   GO 
   CREATE PROCEDURE sp_help_revlogin_2000_to_2005 
   
   @login_name sysname = NULL, 
   @include_db bit = 0, 
   @include_role bit = 0 
   
   AS 
   DECLARE @name sysname 
   DECLARE @xstatus int 
   DECLARE @binpwd varbinary (256) 
   DECLARE @dfltdb varchar (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 STATIC FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] <> 'sa' 
   ELSE 
   DECLARE login_curs CURSOR FOR 
   SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
   FROM master.dbo.sysxlogins 
   WHERE srvid IS NULL AND 
   [name] = @login_name 
   
   OPEN login_curs 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   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 '' 
   PRINT '' 
   PRINT '/***** CREATE LOGINS *****/' 
   
   WHILE @@fetch_status = 0 
   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 = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')' 
   PRINT @tmpstr 
   SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS' 
   PRINT @tmpstr 
   END 
   END 
   ELSE 
   BEGIN -- SQL Server authentication 
   EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT 
   
   IF (@binpwd IS NOT NULL) 
   BEGIN -- Non-null password 
   EXEC sp_hexadecimal @binpwd, @txtpwd OUT 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED' 
   END 
   ELSE 
   BEGIN -- Null password 
   SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=''''' 
   END 
   
   SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   
   IF @include_db = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET DEFAULT DATABASES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']' 
   PRINT @tmpstr 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   IF @include_role = 1 
   BEGIN 
   PRINT '' 
   PRINT '' 
   PRINT '' 
   PRINT '/***** SET SERVER ROLES *****/' 
   
   FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   
   WHILE @@fetch_status = 0 
   BEGIN 
   PRINT '' 
   SET @tmpstr = '-- Login: ' + @name 
   PRINT @tmpstr 
   
   IF @xstatus &16 = 16 -- sysadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &32 = 32 -- securityadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &64 = 64 -- serveradmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &128 = 128 -- setupadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &256 = 256 --processadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &512 = 512 -- diskadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &1024 = 1024 -- dbcreator 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator''' 
   PRINT @tmpstr 
   END 
   
   IF @xstatus &4096 = 4096 -- bulkadmin 
   BEGIN 
   SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin''' 
   PRINT @tmpstr 
   END 
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
   END 
   END 
   
   CLOSE login_curs 
   DEALLOCATE login_curs 
   RETURN 0 
   GO
   
   exec sp_help_revlogin_2000_to_2005 @login_name=NULL, @include_db=1, @include_role=1
   GO

2. Save the output, and then paste and run the output in SQL Server Management Studio on the destination SQL Server 2005.

Note If the source SQL Server contains a login that has a blank password, the output contains a statement that resembles the following.

CREATE LOGIN LoginName WITH PASSWORD = '', CHECK_POLICY = OFF, SID = MySID




How to Transfer Logins Between SQL Servers( Main Link)

When planning a side by side upgrade, or building a replacement SQL Server to upgrade the 

hardware only, one of the tasks that you can be faced with is how to transfer the login accounts 
from one server to another. Since each SQL Login has a unique SID assigned to it and database 
users map to these SID's, what can often happen when you create a database login on a new 
server and then restore the database that used that login previously on a server is that the 
database user no longer maps to the correct Database Login. 

Of course this little problem can be fixed with the use of the spchangeusers_login system
stored procedure, and remap the database user to the SID of the new matching login on the 
SQL Server, but Microsoft has provided a much simpler method of transfering logins. There are
two Knowledge Base articles that provide scripts that create two database objects to script out 
the users in a manner that allows their recreation with the same SID.

To do any one of the following tasks:

• You transfer logins and passwords from SQL Server 7.0 to SQL Server 7.0.
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2000.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2000.
• You transfer logins and passwords from SQL Server 7.0 to SQL Server 2005.
• You transfer logins and passwords from SQL Server 2000 to SQL Server 2005.

Use the scripts on the following link

How to transfer logins and passwords between instances of SQL Server - Part 1(if sorce server is sql 7 or 2000)

Source Link: http://support.microsoft.com/kb/246133.


To do the following task:

• You transfer logins and passwords from SQL Server 2005 to SQL Server 2005/2008/2012.

Use the scripts on the following link

Method: 1

Transfer logins from one SQL Server 2005 instance to another?

Method: 2

How to transfer logins and passwords between instances of SQL Server - Part 2(If sorce server is sql 2005 , sql2008  and sql2012)

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

SQL Server:Finding the Orphaned Users

Orphaned user is a common problem in SQL server world.When I was working on internal security audit of our production servers, I realized that sp_change_users_login with report option will not work with users associated with the windows login.

Who is an Orphan Users?

An user in a database is called orphaned when the associated login does not exists in the server or login has different SID. The first scenario can happen when you delete a login from the server. For example you created a login for an employee Mydomain\Lastname.Firstname  and given access to couple of databases.Later this employee left the company or moved to different department.As a part of process we will delete his/her login (Mydomain\Lastname.Firstname) from the server. Now the databases on which he had access will have orphaned users.

The second scenario will happen when you restore the database from one environment (instance) to  another environment(instance) or when you drop the login and create it again.For example you have login Mydomain\Lastname.Firstname in two instances namely INST1 and INST2. This login has db_owner right on one of the database (MyDb) in INST1. Now you have taken backup of MyDb and restored it on the INST2 and surprisingly Mydomain\Lastname.Firstname will not be able to access the restored database. If he/she tries to access the database , SQL server throw  an error

Msg 916, Level 14, State 1, Line 1
The server principal "Mydomain\Lastname.Firstname" is not able to access the database "MyDb" under the current security context.

When you try to create a user on the restored database for Mydomain\Lastname.Firstname , it will throw an error as below

Msg 15023, Level 16, State 1, Line 1
User, group, or role ' Mydomain\Lastname.Firstname ' already exists in the current database.

This is happening because the SID of the login and user in the database is not matching. You can check this by examining the catalog views sys.server_principals and sys.database_principals.

1.SQL Server:Finding the Orphaned Users

2.Find SQL Orphan Users and Fix using ALTER USER 

3.Cleaning up orphaned users in SQL Server