Break the link between a Database User and SQL Server Login

sql-server sql-server-2016 add tag

John K N (imported from SE)

When restoring database from once instance to another one sometimes has to relink SQL Server Logins to the Database User. 

This is normally achieved with one of the following procedures.

### Deprecated sp_change_users_login Stored Procedure

    use <database>
    go
    sp_change_users_login 'Update_one', '<datbase_user>', '<sql server login>' 
    go

### ALTER USER Command

    use <database>
    go
    ALTER USER [<datbase_user>] WITH LOGIN = [<sql server login>]
    go


These commands will re-link an orphaned Database User to a corresponding SQL Server Login.

# Question

Is it possible to break this relationship without deleting either the SQL Server Login or the Database User?

### Reasons

- Deleting the Database User will remove the permissions in the database.
- Deleting the SQL Server Login will remove the password. (Hashed value; unknown to DBA)
- I linked a Database User to a SQL Server Login that is a **Windows System Account**.

### Research

I have had a look at the `sys.database_principals` and `sys.server_principals` DMVs, but they are not modifiable.

Top Answer

John K N (imported from SE)

Tony's answer was initially pretty near, but only worked for Native SQL Server Logins and not for Windows Authenticated SQL Server Logins. However, the general idea is the same.

1. Create a Windows account on the local server:

         C:\> NET USER <account> /ADD
 
2. Create a Windows Authenticated SQL Server Login:  

        USE [master]
        GO
        CREATE LOGIN [<server>\<account>] FROM WINDOWS WITH DEFAULT_DATABASE = [master]  
        GO

3. Switch to the database in question and link the Windows Authenticated SQL Server Login to the desired Database User:  

    > **Caution**: This step will rename the `[<database_user>]` in the database you are in to the Windows Authenticated SQL Server Login name: `[<server>\<account>]`. Jot the name down to ensure you can rename back.

        USE [<database>]
        GO
        ALTER USER [<database_user>] WITH LOGIN = [<server>\<account>]
        GO



4. Drop the previously created Windows Autheticated SQL Server Login:  

        USE [master]
        GO
        DROP LOGIN [<servername>\<account>]
        GO

5. Rename the Database User that was renamed during step 3. to the previous name:  

        USE [<database>]
        GO
        ALTER USER [<servername>\<account>] WITH NAME = [<database_user>]
        GO

6. Delete the local account that was created for this purpose:  

         C:\> NET USER <account> /DELETE


After following these steps, the `[<database_user>]` is no longer linked to a Windows Authenticated SQL Server Login.

> **Please Note**  
> There is one major caveat to this solution. If you can't actually remove the Windows Authenticated SQL Server Login from the SQL Server and you have to create a new local `<account>` to remap and drop, then the SID of the Database User is changed in the database to reflect the manipulation.

Answer #2

Tony Hinkle (imported from SE)

You can accomplish this in a roundabout way be creating a temporary login, remapping the user to the temporary login, and then dropping the temporary login.  For a SQL login:

    USE [master]
    CREATE LOGIN [temp_user] WITH PASSWORD=N'asdf' MUST_CHANGE, 
        DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
    ALTER LOGIN [temp_user] DISABLE
    
    USE [test_database]
    ALTER USER test_user WITH LOGIN = [temp_user]
    DROP LOGIN [temp_user]

For a Windows authenticated login/user, you would need to create a temporary Windows account, and then delete it, so it's not a 100% T-SQL solution:

    -- Create a Windows account with a name of Temp_User
    USE [master]
    CREATE LOGIN [COMPUTERNAME\Temp_User] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    ALTER LOGIN [COMPUTERNAME\Temp_User] DISABLE
    
    USE [test]
    ALTER USER [COMPUTERNAME\Existing_User] WITH LOGIN = [COMPUTERNAME\Temp_User]
    --Delete the Temp_User Windows account

2 Answers