Chapter 2 User authentication, Authorization, and Security (11): Correcting login mapping errors in restored databases __ database

原文出处：http://blog.csdn.net/dba_huangzj/article/details/39496517，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

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

Preface:

If you move a non-inclusion database from one server to another, whether it is a backup restore or a detached add-on, it can cause the SQL user to become orphaned, meaning that they do not have a corresponding login association. Because the mapping between the login name and the user is based on SIDS, even if the new server has the same login name, the user cannot be identified because of the different SIDS, creating an orphaned user.

If you migrate in the same domain, the mapping problem only affects SQL logins because the SIDs for domain accounts (Windows authentication) are the same in the Active Directory.

implementation:

You can use the following steps to examine and fix the orphaned problem of migrating a database to a new server by restoring or attaching the form:

1. Identify:

SELECT  dp.name, 
        dp.sid 
from    sys.database_principals dp-left 
        JOIN 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. Repair:

ALTER USER fred with LOGIN = Fred

principle:

There are several ways to avoid or fix orphaned problems when the inclusion database is not involved, and you can use system stored procedures: sp_change_users_login for versions prior to SQL Server SP2. Implementation, such as:

Use marketing; 
exec sp_change_users_login @Action = ' the ";

If an orphaned account exists, the names and SIDs of these accounts will be listed and can be repaired 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, and if the sign-in name does not exist, it is 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 null in the returned result means that the onwer of the database is also orphaned, you need to fix it using the following statement:

ALTER AUTHORIZATION on database::marketing to SA;

Next: http://blog.csdn.net/dba_huangzj/article/details/39548665