SQL Server isolated user Solution

Isolation of SQL Server users is a common problem. I have summarized it in detail today and hope it can help you.

When the user database is migrated from one SQL Server using backup and recovery methods to another server. After the database is restored, some other database users defined by the user cannot continue to use it on the new server. Especially for some SQL Server accounts, these are isolated users.

The Security Management of SQL Server users is divided into two layers: The entire SQL Server server layer and each database layer. A user has an account on each layer and is assigned different rights on both layers. At the server layer, you can use login to manage the entire SQL Server server, enable tracking, modify SQL server security configurations, and back up all databases. At the database layer, a database user can set permissions for a specific database, such as reading and writing, modifying table structures, and defining stored procedures.

Server-Level Security is set on the login account of the server. You can query the view SYS. server_principals in the master database for information about all logon accounts.

The database layer has the concept of "Database User. The security of the internal objects of each database, such as reading and writing tables, whether or not to run or modify stored procedures, is granted to database users and stored in the database. You can query SYS. database_principals to learn user information.

The SQL Server Logon account must correspond to a database user before it can be accepted by the database. This is to match the SID in the User Database SYS. database_principals with the SID in the master Database SYS. server_principals. The name of a login account and a database user can be different, but the Sid must be the same.

After the user database is restored to a new server, the master Database SYS. server_principals does not have this account. However, the user database also has this database user. This user is "isolated.

Solution:

1. During backup, the system database should be backed up together, and the recovery time should also be restored.

2. Use sp_change_users_login to change the user

To detect isolated users:

 
User<Database_name>;
Go;
Sp_change_users_login@ Action='Report';
Go;
 

 

Restore isolated users:

 User <  Database_name  >  ;
  Go  ;
Sp_change_users_login  @ Action  =  '  Update_one  '  ,  @ Usernamepattern  =  '  <Database_user>  ' ,  @ Loginname  =  '  <Login_name>  '  ;
  Go  ; 

 

It must be noted that sp_change_users_login can only be reconnected to the SQL Logon account. The Database User corresponds to a Windows login account. If the SID is different, the domain also changes and cannot be connected together in this way.

 

More advanced methods

 

After multiple exclusion of isolated users, it is found that several isolated users are usually present at the same time, while the efficiency of manual exclusion is too low and error-prone. Therefore, a smart storage process is written, exclude all isolated users in the current database at one time:

 

 Declare  @ Username   Nvarchar ( 50 )

 Create   Table # Temp_user (
Username Nvarchar ( 50 ),
USERSID Int 
)

 Insert   Into # Temp_user Exec Sp_change_users_login @ Action  =  '  Report  ' 

 Declare Temp_cursor Cursor   For 
 Select Username From # Temp_user

 Open Temp_cursor
 Fetch   Next  From Temp_cursor Into   @ Username 
 While ( @ Fetch_status  =  0 )
 Begin 
 Exec Sp_change_users_login '  Auto_fix  ' , @ Username ,Null , @ Username ;
 Exec Sp_change_users_login @ Action  =  '  Update_one  ' , @ Usernamepattern  =  @ Username , @ Loginname  =  @ Username ;
Fetch next from temp_cursor into @ username 
 End  
 Close Temp_cursor
 Deallocate Temp_cursor
 Drop   Table # Temp_user

 

We can see from the processing of "isolated users:

1. SQL Server Security is divided into two layers: Server and database

2. It is important to back up and restore the database.

 

Source: SQL Server isolated user Solution