11

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.

1
  • Depending on the environment, maybe a preemptive approach could fit. If you know two instances will have db moving across, create users with same ids and/or copy them with the procedure outlined by ms support.microsoft.com/en-us/help/918992/… with such an approach no remapping should be required
    – Paolo
    May 17 '20 at 9:48
8

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
6
  • You beat me to it. I didn't think anybody would answer that fast. ;-) I was already looking along that avenue. Green Tick Mark is all yours as soon as I have tested it out. Do you want to add the hint to the answer? Or shall I?
    – John K. N.
    May 15 '20 at 13:51
  • I'm not smart enough to know what hint you're referring to, so you can add it. :) May 15 '20 at 13:53
  • 1
    Oh crap--I put my favorite password in the answer! May 15 '20 at 13:56
  • OK. I have a special case. Your solution doesn't work, because I have linked a Windows Account (SQL Server Login) with a Database User.
    – John K. N.
    May 15 '20 at 14:00
  • 1
    The Windows SID is stored as the database user. You can remap a Windows user to a different Windows login (at least I was able to do it on my 2017 instance), but it will still be mapped to that new Windows account. So you could remap it, then disable or delete the Windows account, or in SQL Server disable that Windows login (if you don't want to disable/delete it from Windows). May 15 '20 at 14:24
7

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.

1
  • Was just doing the same thing... May 15 '20 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.