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