Sql-server – Break the link between a Database User and SQL Server Login

permissionsSecuritysql serversql-server-2016

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.

Best Answer

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
Related Question