Chapter 2 User Authentication, Authorization, and Security (11): fixed the logon ing error in the restored database, authentication

Chapter 2 User Authentication, Authorization, and Security (11): fixed the logon ing error in the restored database, authentication
Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

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

 

Preface:

 

If you move a non-contained database from one server to another, whether it is backup restoration or detach, SQL users may become isolated users, meaning they have no corresponding logon Association. Because the SID ing between login names and users is based on SID, even if the new server has the same login name, the user cannot be identified because of the different SID, thus forming an isolated user.

If you migrate data in the same domain, the ing problem only affects SQL logon, because the domain account (Windows Authentication) SID is the same in the Active Directory.

 

Implementation:

 

You can use the following steps to check and fix the isolated problem after migrating the database to the new server through restoration or appending:

1. recognition:

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:

 

When a database is not involved, there are several methods to avoid or solve the isolated problem. For versions earlier than SQL Server 2005 SP2, you can use the system stored procedure: sp_change_users_login. Implementation, such:

USE marketing; exec sp_change_users_login @Action='Report';

 

If an isolated account exists, the names and SID of these accounts are listed. You can use update_one or auto_fix to fix the problem:

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';

Auto_fix is automatically mapped to the same login name. If the login name does not exist, it is created and uses the value defined in @ password as the new password.

 

More:

 

In many cases, the database's onwer may also become isolated. You can use the following statement to check:

SELECT SUSER_SNAME(owner_sid), name FROM sys.databases;

 

If the first row in the returned result is null, the database's onwer is also isolated. You need to use the following statement to fix the problem:

ALTER AUTHORIZATION ON DATABASE::marketing TO sa;

Next article:

Who can help translate a piece of Computer English?

IT seems that you are an IT industry. You cannot completely understand it. As you said, only some details cannot be understood. We recommend that you only ask what you really don't understand. In this way, more people can help you solve the problem, and you don't have to spend a lot of time identifying the answers as machine translation.

For example, you must know that SW is software, not Southwest China.

Benefits of using Windows2000server

Write composition? Refer to the following changes.

Top 10 Reasons Why to Upgrade from Windows 2000 Server to Windows Server 2003 R2
Windows Server 2003 R2 builds upon the increased security, reliability, and performance provided by Windows Server 2003 Service Pack 1 (SP1) to provide a more secure and dependable platform on which to deliver business-critical applications and Web services. at the same time, Windows Server 2003 R2 is easier to manage and integrate into existing environments. this page describes the major new features and improvements encoded in Windows Server 2003 R2.

Centralize user authentication and authorization

Introduced in Windows 2000, the Active Directory directory service simplifies the administration of complex network directories and makes it easy to locate resources on even the largest networks. this enterprise-class service is scalable, is built on Internet-standard technologies, and integrates with the Standard, Enterprise, and Datacenter editions of Windows Server 2003 R2.
Windows Server 2003 R2 provides numerous features-of-use improvements to Active Directory and new features, including cross-forest trusts, the ability to rename domains, and the ability to deactivate attributes and classes in the schema so that their definitions can be changed.

Simplify end user policy management

Administrators can use Group Policy to define the settings and allowed actions for your users and computers. in contrast with local policy, organizations can use Group Policy to set policies Ies that ...... remaining full text>