Break the link between a Database User and SQL Server Login

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 
go
sp_change_users_login 'Update_one', '', '' 
go

ALTER USER Command

use 
go
ALTER USER [] WITH 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.

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

StackExchange Database Administrators Q#267219, answer score: 8