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.
Is it possible to break this relationship without deleting either the SQL Server Login or the Database User?
- 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.
I have had a look at the
sys.server_principals DMVs, but they are not modifiable.