39

When I execute this SQL:

USE ASPState
GO
IF NOT EXISTS(SELECT * FROM sys.sysusers WHERE NAME = 'R2Server\AAOUser')
CREATE USER [R2Server\AAOUser] FOR LOGIN [R2Server\AAOUser];
GO

I get the following error:

The login already has an account under a different user name.

How do I know what this different user name is for my login account?

Mat
  • 10,079
  • 4
  • 42
  • 40
Jack
  • 2,509
  • 14
  • 36
  • 42

3 Answers3

40

This means that the login [R2Server\AAOUser] is already mapped to a user in that database. Or, in other words, another database user is using this login. You can see what database user is using your login with the following query:

use YourDB
go
SELECT su.name as DatabaseUser
FROM sys.sysusers su
join sys.syslogins sl on sl.sid = su.sid
where sl.name = 'test' -- login

PS: a version of the script that doesn't use the compatibility views:

Select sp.name as LoginName, sp.type_desc as LoginType,
    dp.name as DBUser, dp.type_desc as UserType
from sys.server_principals sp
join sys.database_principals dp on dp.sid = sp.sid
where sp.name = 'test' -- your login
Marian
  • 15,531
  • 2
  • 60
  • 74
  • Ok, I see the value dbo being displayed. Strange, I don't remember that I have use the dbo for my account R2Server\AAOUser. Wondering what I should do next. – Jack Feb 22 '13 at 09:06
  • 4
    Have you created the database using that login to connect to the server? If yes, then you're the db owner of it and don't need to create another user. You're already set. – Marian Feb 22 '13 at 09:16
  • Actually, I used the command aspnet_regsql -E -S .\MSSQLSERVER_R2 -ssadd to create the ASPState database. I think probably, I am already set, even though, I even don't realize it in the first place. – Jack Feb 22 '13 at 09:18
  • Guess what: "-E -> Authenticate with current Windows credentials." :-) – Marian Feb 22 '13 at 09:29
  • 4
    I would use sys.server_principals and sys.database_principals. sysusers and syslogins are only there for backward compatibility. – Aaron Bertrand Feb 22 '13 at 14:23
  • @AaronBertrand Thanks, I've edited the answer to use the new system tables. – Marian Feb 22 '13 at 14:44
  • Shouldn't this be and type = 'U' to confirm that it's a user or is that redundant? – Pxtl Oct 04 '19 at 15:36
4

It's a 'metadata thing' ...

Sometimes the database user gets 'corrupted' over the course of whatever is going on in that DB. (I've seen similar behavior if the DB gets restored, and the roles in the restored copy differ from what the one you overlaid contained. This is why I tried below, which fixed the problem for me.)

  1. Open up the Login properties in SSMS --> (Security | Logins | failing UserID | Properties | User Mapping). You'll probably see the DB's already checked and has Roles assigned (like perfectly normal).

  2. Note the permissions on the DB giving the error, just for reference.

  3. Uncheck that DB and save the login.
  4. Now re-run your query to add the Login/Role into the target DB. It should work just fine.
CK in CLE
  • 41
  • 1
  • 2
    when I get to step 3, I get: Cannot drop the user 'dbo'. (Microsoft SQL Server, Error: 15150) – bkwdesign Dec 21 '17 at 19:10
  • 3
    I also got the Cannot drop the user 'dbo'. error message. I ran this script, which removed the problematic user mapping and fixed the issue: USE DATABASE_NAME; ALTER AUTHORIZATION ON DATABASE::DATABASE_NAME TO [sa]. For more information this is the source I used: http://blog.sql-assistance.com/index.php/cannot-drop-the-user-dbo – SherlockSpreadsheets Mar 01 '19 at 14:04
  • I joined just to upvote this comment. The ALTER AUTHORIZATION fix was the one that worked for me, after spending 15 minutes poking around in front of clients. I'd seen this fix before but couldn't find it in my notes. – Rich Moss Jun 13 '23 at 02:35
1

I've already met with a similar issue: I mistakenly added a user in a database, without specifying the DOMAIN prefix, i.e. "joe", instead of "DOMAINX\joe". After this mistake, SSMS will refuse to rename or drop the username with a cryptic error mesage regarding a "principal" whatever.

Solution:

  1. In SSMS Go to Security / Logins, select the user in question.
  2. Select User Mappings.
  3. In the User Column, just rename it properly for each mapped database.
  4. Press OK and go back again to the same dialog box, just to verify. Voila!
alejandrob
  • 125
  • 4