Getting "The login already has an account under a different user name" when trying to map a user to a SQL Login

Question

I have a SQL Login on an Azure SQL server for a particular user account called "azsqlreleaseadmin". I try to map this login into a new database user. The database has been released to the environment using ADO and a dacpac file. The dacpac file contains no definition of the azsqlreleaseadmin user, I want to add it after. So tried to run:

CREATE USER azsqlreleaseadmin FOR LOGIN azsqlreleaseadmin

As far as I can see, this should work, but I get the error:
Msg 15063, Level 16, State 1, Line 22
The login already has an account under a different user name.

I also tried using a different username to check if it was a naming issue:

CREATE USER azsqlreleaseadmin2 FOR LOGIN azsqlreleaseadmin

Same result.

I ran this to check if the user exists:

select * from sys.database_principals where type='U' or type = 'S'

Which did not return the user. When running this on the master database, I see the login:
SELECT * FROM sys.sql_logins

I ran this to try and delete the user on the database.
DROP USER azsqlreleaseadmin

Which returns:
Cannot drop the user 'azsqlreleaseadmin', because it does not exist or you do not have permission.

I am using the Server Admin account, so definitely have permission. I also mapped the server Admin account as a new user, which worked fine.

I have looked through a dozen stack overflow and old MSDN resolutions, they all suggest the user exists on the database. But clearly it doesn't according to the above query.

It seems like there is some sort of problem mapping creating SQL Users on Azure SQL databases from Logins? I did this a few months ago without issues.

Answer 1

Run this query on master database. It will give you a list of logins that exist at the server level.

SELECT A.name as userName, B.name as login, B.Type_desc, default_database_name, B.*   
FROM sys.sysusers A   
    FULL OUTER JOIN sys.sql_logins B   
       ON A.sid = B.sid   
WHERE islogin = 1 and A.sid is not null  

At the database level run the following query to list the users that have been created. Maybe below query shows you what database user at the database level is associated with the specific login on the master database.

SELECT DB_NAME(DB_ID()) as DatabaseName, * FROM sys.sysusers