Autofix won’t fix an orphaned user

One of my logins would not repair after a restoration in my development environment.

When the SQL login (mismatched user) exists and you use autofix parameter then EXEC sp_change_users_login 'Auto_Fix', 'phone' procedure will fix it without errors.

But consider the situation when a user "phone" exists in the database but the login "phone" doesn't exist on the server. You need to provide two more parameters (loginname and password) for the procedure to work. You need to use null for loginname and you need to provide a password. As follows

EXEC sp_change_users_login 'auto_fix','phone',null,'Pa$$w0rd'

Now the procedure will search for the login 'phone'. If it exists it will map with the 'phone' login.  If not, it will create a login with the password specified and maps the user to the login.

Comments Closed.