But if you need to add just one database app that requires a separate login beyond the Windows login, here's how.
First a few definitions.
SERVER vs. DATABASE
SQL Server (in my case express 2008 R2) is not a Database. In the management studio, when you log into the management studio, you can see that .\SQLEXPRESS (the root node) is called a server. It is important to read this article carefully when I talk about servers vs. databases, as it makes a difference.
LOGIN vs. USER
This is a little more blurry, but a login is not much more than a name, password, and a set of permissions to access the server. A user exists in databases, and has specific roles and permissions in an individual database.
Getting Started
In the Mode
First, make sure your server is in the mode (mood?) to use both kinds of authentication.
Open SQL Server Management Studio and right-click the SERVER name. Pick properties.
Restarting SQL Server
Return to the SQL Server Management Studio and right click the Server Name.
Create a Server Login
When we create logins, in general we want to create one login per user. First, log into management studio using windows authentication (or however you normally gain admin access). Under the server tree (not a database tree) select Security- Logins and pick New Login.
Make sure to add a password and select the default database.
Select the checkbox by your database.
Enter dbo as the default schema
Check every permission that doesn't contain the word deny.
Note that there are some additional roles in my database that I had added previously.
Using witch-hazel and fairy dust, Management studio will now create your LOGIN to the SERVER, and your USER in the DATABASE.
Look in the Databases tree and find the user created by the wizard.
Testing
To test your new user/login, open a second copy of SQL Server Management Studio.
Change to SQL Server Authentication and enter your user login info.
Now it's easy to use this login in your programs to access this database. It's also easy to set database level permissions, roles etc.
in C# to create a connection string to access the database do this:
Bryan Valencia is a contributing editor and founder of Visual Studio Journey. He owns and operates Software Services, a web design and hosting company in Manteca, California.
