• notice
  • Congratulations on the launch of the Sought Tech site

A user or role already exists in the current database of sql server

Reasons and solutions:

  1. The difference between the login user name in sql server and the database user user name. The login user name is used for user authentication, and the database user user name is used for database access and authority verification.

  2. Login is associated with the user through a security identifier (SID). When restoring the database to another server, the database contains a set of users and permissions, but there may not be a corresponding login or the users associated with the login may not be the same user. This situation is known as the existence of "orphaned users."

  3. At this time, it is not possible to 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 SQL Server will report "Error 15023: There is already a user or role in the current database".In order to solve this problem, Need to call the system stored procedure sp_change_users_login, the specific usage is as follows:
    Use  database name
    sp_change_users_login'update_one',' user name' ,' user name'
    where the database name is the database with orphaned users, update_one is the parameter of the stored procedure, which means that only one is processed User, the first user name is the database user, and the latter user name is used for database login.The above SQL command means to reconnect the database user user name with the server login user name . In this way, the database can be used normally.


Technical otaku

Sought technology together

Related Topic


Leave a Reply