Sql-server – Best way to remap database login to new database user and keep previous database user

authenticationsql serversql-server-2008-r2users

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?

Best Answer

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?

You can't create different database users mapped to the same login because a database user can be mapped to no more than one login. The CREATE USER statement for dbuser02 failed so dbuser01 is still mapped to aduser01 and dbuser02 was not created.

I'm not exactly sure what you are trying to accomplish. If your intent is to selectively grant database access to some, but not all, members of the mydomain\group01 and specify an individual default schema for each user, try the script below.

--add all members of mydomain\adgroup01 as role members
EXEC sp_addrolemember N'db_mydbrole', N'mydomain\adgroup01';
--create a default schema and user for desired mydomain\adgroup01 members
CREATE SCHEMA [aduser01] AUTHORIZATION [aduser01];
CREATE USER [mydomain\aduser01] WITH DEFAULT_SCHEMA=[aduser01];
CREATE SCHEMA [aduser02] AUTHORIZATION [aduser02];
CREATE USER [mydomain\aduser02] WITH DEFAULT_SCHEMA=[aduser02];
Related Question