SQL Server SDE during database migration
Sqlserver2008 user group or role ***** 'existing problems in the current database
SQL Server SDE during database migration
When you add a user to a database, the following message is displayed:
The user, group, or role '*****' already exists in the current database. (Microsoft sqlserver, error: 15023)
Problem Analysis: During the database migration test platform,
After the database is migrated between SQL Server servers running on the two servers,
A normal user on the source server cannot log on to the target server.
Solution:
When a database is restored to another server, the database contains a group of users and permissions,
However, there may be no corresponding logon or the user associated with the logon may not be the same user.
This situation is called "isolated user ".
In this case, you cannot solve the login problem by creating a new login or granting the "user" permission of the corresponding database to the login with the same name,
Because sqlserver reports "error 15023: a user or role already exists in the current database ",
To solve this problem,
Call the system stored procedure sp_change_users_login,
The usage is as follows:
1. Open SQL Server Management studio,
Right-click "Database"> "new query" to create a new query.
Enter the following SQL script:
// ======================================
Use [database name]
Go
Sp_change_users_login 'Update _ one', 'aaa', 'aaa'
// ======================================
Sp_change_users_login 'Update _ one', 'aaa', 'aaa'
Run the script.
Note: update_one is the parameter of the stored procedure,
Indicates that only one user is processed, and the previous AAA is a "user ",
The next AAA is "login ",
The preceding SQL statement re-connects the server Logon "AAA" to the Database User "AAA.
Use SDE
Go
Sp_change_users_login 'Update _ one', 'sde', 'sde'
Go
Use cbjyq_flowdb
Go
Sp_change_users_login 'Update _ one', 'cbjyq _ flowdb', 'cbjyq _ flowdb ';
Go
Use SDE
Go
Sp_change_users_login 'Update _ one', 'cbjyq _ flowdb', 'cbjyq _ flowdb ';
Go
Use cbjyq_flowdb
Go
Sp_change_users_login 'Update _ one', 'sde', 'sde ';
Go
-- The -- end!