Chapter 2 User authentication, Authorization, and Security (11): Fix a login mapping error in a restored database

Original source: http://blog.csdn.net/dba_huangzj/article/details/39496517, featured catalogue:http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, be published in the form of "original" or used for commercial purposes, and I am not responsible for any legal liability.

Previous article: http://blog.csdn.net/dba_huangzj/article/details/39473895

Objective:

If you move a non-contained database from one server to another, whether it is a backup or detach, it can cause SQL users to become orphaned users, meaning they do not have a corresponding login association. Because the mapping between the login and the user is Sid-based, even if the new server has the same login name, but because of the different SID, it will also make the user unrecognized, resulting in orphaned users.

If you are migrating in the same domain, the mapping problem only affects SQL logins because the SID of the domain account (Windows authentication) is the same in the Active Directory.

Realize:

You can use the following steps to check and fix orphaned issues after migrating a database to a new server through a restore or an attached form:

1. Identification:

SELECT  Dp.name,         dp.sid from    sys.database_principals DP left         joins sys.server_principals sp on dp.sid = Sp.sid WHERE   Sp.sid is NULL and         dp.type_desc = ' sql_user ' and         dp.principal_id > 4;

 

2. FIX:

ALTER USER Fred with LOGIN = Fred

Principle:

There are several ways to avoid or remediate orphaned issues when you do not involve a containing database, and you can use system stored procedures: sp_change_users_login for versions prior to SQL Server 2005 SP2. implementations, such as:

Use marketing; exec sp_change_users_login @Action = ' report ';

 

If there are orphaned accounts, the names and SIDs of these accounts will be listed, which can be fixed using Update_One or Auto_Fix:

EXEC sp_change_users_login @Action = ' Update_One ', @UserNamePattern = ' Fred ', @LoginName = ' Fred '; EXEC sp_change_users_login @Action = ' Auto_Fix ', @UserNamePattern = ' Fred ', @Password = ' I am s3cr3t! '; EXEC sp_change_users_login @Action = ' Auto_Fix ', @UserNamePattern = ' Fred ';

Where Auto_Fix is automatically mapped to the same login name, if the login does not exist, it will be created and the value you define in @password as the new password.

More:

In many cases, the onwer of the database may also become orphaned, which can be checked with the following statement:

SELECT SUSER_SNAME (OWNER_SID), name from sys.databases;

If the first behavior in the returned result is null, it means that the onwer of the database is also orphaned and needs to be fixed using the following statement:

ALTER AUTHORIZATION on database::marketing to SA;

Next article:

Chapter 2 User authentication, Authorization, and Security (11): Fix a login mapping error in a restored database