In MS-SQL Server, How to change a db User from WithoutLogin to be associated with a login

Question

I have an existing database. It has 1 non-default user (that is Without Login), and that user is the dbo.

How do I associate this user with a login?

Answer 1

Here are the steps

1. Create the Login on the server. This means that you now have a Login on the server, and a User in the database
2. Connect the UserName from the database to the Login from the server as follows:

First, run a report in the database to find all orphaned users

EXEC sp_change_users_login 'Report'

Since you already have a Login for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'YourUserName'