Saturday, June 19, 2010

sql server: "User, group, or role XXXX already exists in the current database." even after deleting user and recreating

A database user existed without an associated sql server login.
WebSphere wants login to match the schema name (up to now it seems okay...)

  • tried to create a user with this login -- failed because 'user or role' already exists. or principal, I forget the error.
  • delete that login. fail because its the owner of a schema.
  • edit that schema, assign ownership to another user
  • create login. failed because of user mapping, with a message similar to above.
  • but it was actually created! I saw it later after trying to see if I could find any reference to the user.
  • try to modify the user mapping again. okay for the default schema/database, and I could login okay, but trying to assign the user mapping for database 'master' failed, and I think those SqlJDBCXAUser role settings are necessary.
So I searched google for "User, group, or role" "already exists in the current database." "user mapping" master, and found the following:

SQL Server Tip - Fixing Orphaned Users and ran the following as sa:

sp_change_users_login 'auto_fix', 'UserName'
where UserName is the name of your user.
The row for user 'customizationusr' will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.
checked the login to see if I could make the master mapping... It was already there!
Success. am i certified ms sqlserver dba now?

No comments: