The Problem:
After creating user accounts and restoring an Microsoft SQL Server database onto another, it was discovered that database logins didn’t work. This problem is due to the mapping of database users to SQL Server logins to a database. There is a SID that associates the database user to the login id. The database user ids are stored in the user database that is moved, the sql server login is stored in the master database which is not migrated. Just so you understand the relationship between these two databases and tables issue the following statements (substitute <database_name> with a user database name):
|
1 2 3 |
select name, principal_id, sid from master.sys.sql_logins where type_desc = 'SQL_LOGIN' select name, principal_id, sid from <database_name>.sys.server_principals where type_desc = 'SQL_LOGIN' |
If there is a mismatch on the SID or if there is a missing sql server login, the user will not be able to utilize the database. Such database users are referred to as “orphaned users”.
SQL Server provides an stored procedure “sp_change_users_login” (http://technet.microsoft.com/en-us/library/ms174378.aspx) which will assist with problem by linking a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name.
Verifying the problem
To detect orphaned users, execute the following Transact-SQL statements:
|
1 2 3 4 |
USE <database_name>; GO EXEC sp_change_users_login @Action='Report'; GO |
Fixing the problem
To fix this problem you can do one of the following:
- If you already have a login id and password for this user, fix it by doing the following:
|
1 2 3 4 |
USE <database_name>; GO EXEC sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', @LoginName='<login_name>'; GO |
- If you want to create a new login id and password for this user, fix it by doing:
|
1 2 3 4 |
USE <database_name>; GO EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password' GO |
Note: If a login with the same name does not exist, one will be created. You must specify user and password if the login does not already exist. If the user already exists, you must specify the user parameter (the password parameter will be ignored in this case).
