Why change the user type for the SQL user with disabling SSMS login?

Question

I backed up the database with SQL Server 2008 and restored it to my local computer using SQL Server 2012, now I am trying to log in to the server with a user account with the database to be backed up, and I could not do this.

After searching for the problem, I found that I need 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 figure below, how can I fix this and this is the best way to do what Do I need to run or do I need to add this user to the server level?

Answer 1

I remember how I used to work on this when doing backup / restore on servers. It basically boils down to how SQL Server works. There, SQL Server users and database users (SQL Server users who are database users are represented through mappings). However, they are not the same thing.

The SQL Server user belongs to SQL Server, the database user ONLY belongs to the corresponding database. What happens when you have a database user but not a SQL Server user? You cannot enter SQL Server explicitly.

Thus, after moving the database, I add the user that I need to log in, as for SQL Server users, using SSMS, delete the old database user (he received dependencies associated with him that prevent him from matching) and, finally make a new user in the database by matching my SQL user with the database with the appropriate permissions.

This approach is by no means elegant, but it works for 100% of the time without the need for code, and you should consider a more permanent system if you have automatic backup / restore. For this, I always did it.