Sp_addlogin two solutions if the restored user ID already exists and the SID number is different from the existing Sid

In general, when we restore a database, we need to use sp_addlogin to re-Add the login ID of the database.

 

Run the SQL command: Select Sid from newdb... sysusers where name = 'wbluser' and write down the SID value.

Run the SQL command: sp_addlogin 'wbluser', 'wbl', 'newdb', null, @ Sid, null.

 

 

However, sometimes the log-on ID of the newly recovered database newdb is different from that of the existing olddb user on the computer,

In this case, the user will be prompted to exist during recovery. There are two solutions:

1. Create a New Login ID: wbluser_new, and log in with the new user wbluser_new

Sp_addlogin 'wbluser _ new', 'wbl', 'newdb', null, @ Sid, null

 

2. There is another way: two databases share wbluser.

 

The following SQL code modifies the parameters of the current database:
Use master
Go
Sp_configure 'Allow updates', 1
Reconfigure with override
Go
This statement sets the value of "whether to allow system table modification" of the current database to 1, that is, allows users to modify the system table.
Then, update the wbluser Sid in the table sysusers in the database to the SID of the current service.

Run the SQL command: Select Sid from olddb... sysusers where name = 'wbluser' and write down the SID value.

Update newdb .. sysusers set SID = @ Sid where name = 'wbluser'

In this case, you can use wbluser to log on to the two recovered databases at the same time.