How to fix an MSSQL user with a missing login?

What is the problem?

Either you moved a database from one MSSQL Server instance to another or for another reason  the login assigned to a user is missing. Oddly, SQL Server Management Studio will not allow you to “pick” a new login and fix the user.

Can you come to the point?

MSSQL Server has an impressive amount of system stored procedures which allows you make operations that for some reason the management studio will not allow.

How to fix it?

Leave your database as it is, with a user in it but the user missing its login assignment.

Create a new login with the exactly same name as your user.

Execute the following procedure:

USE [DatabaseName]

EXEC sp_change_users_login 'Auto_Fix', 'ExistingUserName'

Further reading:

System Stored Procedures (Transact-SQL)
sp_change_users_login (Transact-SQL)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.