Here's what my environment looks like:
Active Directory (mydomain.local) - Groups - group01 - Members - aduser01 SQL Instance - Logins - mydomain\group01 - Databases - db01 - Users - dbuser01 (login = aduser01, default schema = dbuser01) - Schemas - dbuser01 - Roles - db_mydbrole
What I'd like to do is create a new database user and schema mapped to login aduser01:
USE [db01] GO CREATE USER [dbuser02] FOR LOGIN [mydomain\aduser01] WITH DEFAULT_SCHEMA=[dbuser02] GO EXEC sp_addrolemember N'db_mydbrole', N'dbuser02' GO CREATE SCHEMA [dbuser02] AUTHORIZATION [dbuser02] GO
However since aduser01 is mapped to dbuser01 it fails with error:
The login already has an account under a different user name (Microsoft SQL Server, Error: 15063)
What is the best way to go about this? If database user dbuser02 is created and mapped to aduser01, what are the possible states dbuser01 could be left in?