SQL Server: How to check if a windows user exists in the database but under a different user name

Question

I am trying to create a user via a T-SQL script in SQL Server 2005/2008. I run the following SQL to check if the user exists and create the user if it does not:

IF NOT EXISTS (SELECT * 
               FROM   sys.database_principals 
               WHERE  name = N'MyDomain\MyUser') 
  BEGIN 
      CREATE USER [MyDomain\MyUser] FOR LOGIN [MyDomain\MyUser] WITH default_schema=[dbo]; 

      SELECT 1; 
  END 
ELSE 
  BEGIN 
      SELECT 0; 
  END  

Unfortunately, this does not work when the windows account MyDomain\MyUser is already in the database but under a different name. An example of this would be if MyDomain\MyUser was the account that created the database then it will already be in the database under the dbo user. When this is the case I get the following error:

Msg 15063, Level 16, State 1, Line 1 The login already has an account under a different user name.

How can I check if the windows user MyDomain\MyUser is already a user in the database even if it is under a different user name?

Answer 1

Do the testing for windows user vs sys.database_principals using the SID rather than the name/user_id:

select * from sys.database_principals where sid = SUSER_SID(N'MyDomain\MyUser')

Since this uses the SID of the login ID, it doesn't matter what name it was mapped to in the database.