You are currently offline, waiting for your internet to reconnect

BUG: Error 15023, 15024 or 21002 Occurs When You Try to Add a Domain User or a Domain Group to the Database

BUG #: 363065 (SQL Server 8.0)
SYMPTOMS
When you try to provide access to a domain user or a domain group to a database by using SQL Server Enterprise Manager, you might receive one of the following error messages:

Error 15023: User or role '<name>' already exists in the current database.
-or-

Error 15024: The group '<name>' already exists in the current database.
-or-

Error 21002: [SQL-DMO]User '<name>' already exists.
CAUSE
This problem can occur when an existing database role or user name is the same as the domain user or group name that you are trying to add to the database.
STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
WORKAROUND
To work around this problem, use either of the following methods:
  • Add the domain user or the domain group at the database level. To do so, follow these steps:
    1. Start Enterprise Manager.
    2. Expand Microsoft SQL Servers, and then expand the SQL Server group that contains your server.
    3. Expand SQL Server, and then expand Databases.
    4. Expand the Database that you want to provide access to the domain user or domain group.
    5. Select, right-click Users, and then click New Database User.
    6. In the Database User Properties - New User dialog box, type the complete domain group or domain user in the Login name text box. For example, domainName\userName or domainName\groupName.
    7. Click OK.
  • Specify the User or Group Name with the Domain Name


    1. Start Enterprise Manager.
    2. Expand Microsoft SQL Servers and then expand the SQL Server group that contains your server.
    3. Expand Security.
    4. Select and right-click Logins, and then click New Login.
    5. In the SQL Server Login Properties - New Login dialog box, type the complete domain group or domain user name in the Name text box. For example, domainName\userName or domainName\groupName.
    6. Click theDatabase Access tab.
    7. Under Specify what databases can be accessed by this login, click to select the database that you want to provide access to.
    8. For all the selected database, replace the user or group name under User with domainName\userName or domainName\groupName.
    9. Click OK.
MORE INFORMATION
When you provide access to a domain user or a domain group to a database by using SQL Server Enterprise Manager, SQL Server runs the sp_grantdbaccess stored procedure.

For example, when you provide access to domain group dmnName\grpName to a database, the following Transact-SQL statement is run:
sp_grantdbaccess N'dmnName\grpName', N'grpName'
Note The @name_in_db (second parameter of the stored procedure) has only the group name.

When the @name_in_db value matches with any of the existing database user or role names, the operation fails with one of the errors mentioned in the "Symptoms" section.

Steps to Reproduce the Behavior

  1. Start Enterprise Manager.
  2. Expand Microsoft SQL Servers, and then expand the SQL Server group that contains your server.
  3. Expand SQL Server, and then expand Databases.
  4. Expand the Database that you want to provide the access to the domain user or the domain group.
  5. Select and right-click Roles, and then click New Database Role.
  6. In the Database Role Properties - New Role dialog box, type GrpName in the Name text box.

    Note: GrpName must exist as a group in the domain.
  7. Click OK.
  8. Under SQL Server, click to expandSecurity.
  9. Select and right-click Logins, and then click New Login.
  10. In theSQL Server Login Properties - New Login dialog box, type the domain group name in the Name text box. For example, dmnName\grpName.
  11. Click the Database Access tab.
  12. Under the Specify which databases can be accessed by this login section, click to select the database that you selected in step 4.
  13. Click OK.

REFERENCES
For more information about SQL Server database logins, visit the following Microsoft Web site:

Creating SQL Server Logins

For more information about the sp_grantdbaccess stored procedure, visit the following Microsoft Web site:

sp_grantdbaccess
Properties

Article ID: 819261 - Last Review: 02/16/2007 06:00:28 - Revision: 2.4

  • Microsoft SQL Server 2000 Standard Edition
  • kberrmsg kbdatabase kbbug KB819261
Feedback