Why change user type to SQL user with login is disabled in SSMS?

Question

I backed up a database from a SQL Server 2008 and restored it to my local machine using SQL Server 2012, now I'm trying to login to the server with the copied database user account and I wasn't able to do so.

After googling the issue I found that I have to change the user type from SQL User Without Login to SQL User with Login but the drop-down list is disabled as you can see in the picture below, how can I fix this and is this is the best way of doing what I need to accomplish or do I need to add this user to the server level?

Answer 1

I remember running into this before when doing backup / restore across servers. Basically it comes down to how SQL Server works. There's SQL Server users & there's database users (SQL Server users who are database users are represented via mappings). They are however not the same thing.

A SQL Server user belongs to the SQL Server, a database user ONLY belongs to the associated database. What happens when you have a database user, but not a SQL Server user? You can't login to SQL Server non-obviously.

Thereby what I do is after moving the database, I add the user I need to login as to SQL Server users using SSMS, remove the old database user (it's got dependencies associated w it that prevent mapping to it) & lastly make a new user on the database by mapping my SQL user to the database w appropriate permissions.

This approach is by no means elegant, but it works 100% of the time w no code needed, & you should consider a more permanent system if you have automated backup / restores happening. For the one off, this is how I've always done it.