Birch Note 6-Xu detects orphaned users and restores orphaned users to new servers resolve database mirroring orphaned user issues

1--Detect orphaned users and restore orphaned users to new server to resolve database mirroring orphaned users problem 2--note: Relink can only link to the SQL login account, cannot link Windows login account 3-- If you do not select Relink to restore to a new server by using the backup master database, you can avoid a database that has 4--hundreds of thousands of database users 5 SELECT * FROM Sys.server_principals 6 select * from S Ys.database_principals 7 8--Detect orphaned Users 9 use Gposdb--to create a mirrored database EXEC sys.sp_change_users_login @Action = ' report ',-- varchar (TEN) @UserNamePattern = null,--sysname12 @LoginName = null,--SYSNAME13 @Password = null--Sysnam E14 GO15 16--Create a new login account with the same name on the server, such as Hengshan, and then relink the Hengshan server login account with the database user Hengshan, 17-their SID is changed to a consistent use GPOSDB19 GO20 EXEC sys.sp_change_users_login @Action = ' Update_One ',--varchar (TEN) @UserNamePattern = ' Hengshan ',--sysname<d atabase_user>22 @LoginName = ' Hengshan ',--sysname<login_name>23 @Password = ' test '--sysname the database user password with The login account password is set to test the password for the Hengshan user of the main library is also Test24---------------SQL2008 auto_ Fix only uses----------------------------------------------GPOSDB30 EXEC sys.sp_change_users_login @Action = ' Auto_Fix ',--varchar @UserNamePattern =hengshan--sysname32------------------------Resolve the problem of database mirroring orphaned users---- -----------------35--executes the following statement in the primary database: master;37 select Sid,name from syslogins;38 39 40--Finds the appropriate database user name and SID, For example: The above ' myuser ' 41--42--executes the following statement in the standby database: master;45 exec sp_addlogin46 @loginame = ' MyUser ', @passwd = ' Test ', @s id = 0x074477739dca0e499c29394fffc4ade449 50--Here the ' LoginName ' is the login name in the primary database, and the SID is the SID that was found by the SQL statement above. 51--Note the password 52 53--so far, the environment of the standby database has been synchronized with the host, and the data in the database is not synchronized

Birch Note 6-Xu detects orphaned users and restores orphaned users to new servers resolve database mirroring orphaned user issues