Shulou(Shulou.com)06/01 Report--

Today, when assigning database permissions to a database account, an error is thrown directly. The details of the error are as follows: user, group, or role''already exists in the current database. (Microsoft SQL Server, error: 15023). After consulting the relevant documentation, it is found that this error may be due to the existence of orphaned users in the database.

When you restore a database to another server, the database contains a set of users and permissions, but there may not be a corresponding login or the user associated with the login may not be the same user. This situation is called the existence of "isolated users". At this point, you cannot solve the login problem by creating a new login or granting "user" permission to the corresponding database with the same login, because SQLServer will report "error 15023: a user or role already exists in the current database."

In order to enable the database account to be authorized to access the database smoothly, the system stored procedure sp_change_users_login built in Sqlserver can be used to solve this problem.

The steps to resolve this problem are as follows:

1. Log in to the Sqlserver management tool, and after connecting to the server successfully, enter the following statement in the new query window to solve the problem.

Use [database name]

Go

Exec sp_change_users_login 'UPDATE_ONE',' user name', 'login name'

This statement maps the user name to the specified login name.

2. For example, if you associate the user1 user in the ADataBase database with the login name user1, you can use the following statement.

Use ADataBase

Go

Exec sp_change_users_login 'UPDATE_ONE','user1','user1'

Extended reading: Sqlserver changes the default port number to increase security.

Note: this original article was first published on the blogger's personal technical site. Original link: Sqlserver users, groups, or roles already exist in the current database _ IT technology fun house.

Blogger personal technical exchange group: 960640092, blogger Wechat official account is as follows:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.