Adding Remote Logins to SQL Server 2012

This should be quite easy to do but it can also be a headache if you're doing it wrong and are lost like I've been the first few times I did it.

The task is creating a remote login for SQL Server 2012 so that can be accessed remotely. I'm not going to get into the details of setting up SQL Server for remote access because there are many other resources on that.

There's a very silly mistake that's easy to make when looking at all the available guides on how to create remote logins that no one ever talks about. According to most guides there should be a "logins" folder under the security folder. They're referring to the security folder on the server instance, not one of the database instances. Don't create a new user for the DB yet, create a new login for the server!

So to create a login by expanding server instance -> security and right click on logins. This will allow you to create a new remote login. Use SQL server authentication mode, which will allow you to type a username and password. The login can be added to a database using the user mappings tab. Don't forget to set up permissions under user mappings for each database, and be sure to choose the right default database or you'll get complaints whenever you try to log in remotely or make queries.

If you modify things in the securables tab you might get an error like this:

So don't do it.

Refresh the database(s) you chose as your mappings in the previous step and your user should be there under server instance -> databases -> your database -> security -> users.

Piece of cake!