I recently had to look up how to create a login in SQL Server, so that I could connect my local Orchard CMS instance to it. I had to look this up for the umpteenth time. And the docs are often a bit too much for me, as they are (I think) catered towards database experts. Which I am not.

So here’s a quick step-by-step guide what I did.

First, if you don’t already have it, you can run a full SQL Server locally. Microsoft provides a Developer edition, if SQL Server Express isn’t enough for you.

Second, some background. SQL Server has two concepts, which confused me for a long time (and still do a bit): users and logins.

A login is a way to authenticate with the server. Not the database, as one server can have multiple databases. There are two options here: you can use a Windows login or a SQL login.

If you use a Windows login, Windows is doing the authentication, and you will use Integrated Security in your connection string. If you use a SQL login, SQL server is doing the authentication, and you will use User Id and Password.

A user, on the other hand, is tied to a database, not the server. A user is linked to a login and contains the permissions for that login on that specific database. Also, this means one login can be linked to multiple users, across multiple databases.

Confused yet? I know I was in the beginning, but I’m sure there are good reasons for this setup. Never mind, here’s what to do to set it all up.

Create a login

In SQL Server Management Studio (also a free download by the way), connect to your database, open the Security folder, right click on Logins and select New Login. Choose SQL Server authentication and specify a username and password (you can choose Windows authentication if it fits your needs). If you’re going to use it for an application, you will probably want to disable the options that force password change and expiry.

Create a user

Now go to your database, open that Security folder, right click on Users and select New User. If you’ve created a SQL Login, choose SQL user with login from the dropdown. Give the user a name, choose the login you created earlier and give it a default schema, if necessary.

Add user membership

Don’t create the user just yet. Go to the Membership tab and give the user a role, so that it can actually do something in the database. Now you can save this user.

Set authentication mode

Finally, if you’ve created a SQL user, you’ll want to enable SQL Server Authentication mode. Right click on your database and select Properties. Choose the Security tab and enable SQL Server and Windows Authentication mode.

Click OK and you’re all set.

To sum it up

Create a login, create a user, set its membership, and set the authentication mode. And now you can use these credentials in your connection string.