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

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

In this article, server A and server B are different servers. Additionally, both server A and server B are running SQL Server 2005. 

Note This information also applies to SQL Server 2008 and to SQL Server 2012.

After you move a database from the instance of SQL Server on server A to the instance of SQL Server on server B, users may be unable to log in to the database on server B. Additionally, users may receive the following error message:

Login failed for user 'MyUser'. (Microsoft SQL Server, Error: 18456)

This problem occurs because you did not transfer the logins and the passwords from the instance of SQL Server on server A to the instance of SQL Server on server B.

To transfer the logins, use one of the following methods, as appropriate for your situation.

Method 1: Log in by using the pre-SQL Server 2000 password

To resolve this issue, ask the user to log in to the server that is running SQL Server by using the pre-SQL Server 2000 login.

Note The password hashing is updated automatically when the user logs in by using the pre-SQL Server 2000 password.

Method 2: Reset the password in SQL Server

To resolve this issue, reset the password in SQL Server, and then script out the login.

Note The password hashing algorithm is used when you reset the password.

Method 3: Create a log in script that has a blank password

To create a log in script that has a blank password, follow these steps:

1. On server A, start SQL Server Management Studio, and then connect to the instance of SQL Server from which you moved the database.
2. Open a new Query Editor window, and then run the following script.
   
   

   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 (514) OUTPUT
   AS
   DECLARE @charvalue varchar (514)
   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 @type varchar (1)
   DECLARE @hasaccess int
   DECLARE @denylogin int
   DECLARE @is_disabled int
   DECLARE @PWD_varbinary  varbinary (256)
   DECLARE @PWD_string  varchar (514)
   DECLARE @SID_varbinary varbinary (85)
   DECLARE @SID_string varchar (514)
   DECLARE @tmpstr  varchar (1024)
   DECLARE @is_policy_checked varchar (3)
   DECLARE @is_expiration_checked varchar (3)
   
   DECLARE @defaultdb sysname
    
   IF (@login_name IS NULL)
     DECLARE login_curs CURSOR FOR
   
         SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
   sys.server_principals p LEFT JOIN sys.syslogins l
         ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
   ELSE
     DECLARE login_curs CURSOR FOR
   
   
         SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
   sys.server_principals p LEFT JOIN sys.syslogins l
         ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
   OPEN login_curs
   
   FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   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 ''
   WHILE (@@fetch_status <> -1)
   BEGIN
     IF (@@fetch_status <> -2)
     BEGIN
       PRINT ''
       SET @tmpstr = '-- Login: ' + @name
       PRINT @tmpstr
       IF (@type IN ( 'G', 'U'))
       BEGIN -- NT authenticated account/group
   
         SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
       END
       ELSE BEGIN -- SQL Server authentication
           -- obtain password and sid
               SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
           EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
           EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    
           -- obtain password policy state
           SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
           SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
    
               SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
   
           IF ( @is_policy_checked IS NOT NULL )
           BEGIN
             SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
           END
           IF ( @is_expiration_checked IS NOT NULL )
           BEGIN
             SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
           END
       END
       IF (@denylogin = 1)
       BEGIN -- login is denied access
         SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
       END
       ELSE IF (@hasaccess = 0)
       BEGIN -- login exists but does not have access
         SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
       END
       IF (@is_disabled = 1)
       BEGIN -- login is disabled
         SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
       END
       PRINT @tmpstr
     END
   
     FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
      END
   CLOSE login_curs
   DEALLOCATE login_curs
   RETURN 0
   GO
   

   
   
   Note This script creates two stored procedures in the master database. The procedures are named sp_hexadecimaland sp_help_revlogin.
3. Run the following statement: 
   
   

   EXEC sp_help_revlogin

   The output script that the sp_help_revlogin stored procedure generates is the login script. This login script creates the logins that have the original Security Identifier (SID) and the original password.
4. On server B, start SQL Server Management Studio, and then connect to the instance of SQL Server to which you moved the database.
   
   Important Before you go to step 5, review the information in the "Remarks" section.
5. Open a new Query Editor window, and then run the output script that is generated in step 3.

Remarks

Review the following information before you run the output script on the instance on server B:

• If you try to create a new SQL Server 2012 login by using a pre-SQL Server 2000 login that is scripted, you receive the following error:
  Msg 15021, Level 16, State 2, Line 1
  Invalid value given for parameter PASSWORD. Specify a valid parameter value.
  Note You receive this error in SQL Server 2012 because of the 16-byte password hash that is supplied for the CREATE LOGIN and ALTER LOGIN statements.
  
  To resolve this issue on a server that is running SQL Server 2012, create a login that has a blank password. To do this, run the following script:
  
  

  CREATE LOGIN [Test] WITH PASSWORD = '', SID = 0x90FD605DCEFAE14FAB4D5EB0BBA1AECC, 
  DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF

  
  After you create the login that has a blank password, the user can change the password at the next login attempt.

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