How can I set up SQL Server 2008 users without Windows/Domain accounts?

Question

I'm trying to do this through the SQL Server Management Studio GUI.

The user accounts will be accessed by applications directly, so I don't want to create Windows user accounts. I'd rather use something like the sa account.

Is there a way of creating users directly in SQL Server?

Answer 1

The first part is to create a login for the new user, if you can't login to the server you can't perform queries on a database.

In MSSMS Expand the Security tab, right click on Login and select New User. Click the SQL Server authentication radio button and fill in the password for the new user. You will probably want to uncheck the box for "Enforce password expiration" and "User must change password at next login" if you are using the login from an application.

Click the User Mapping page in the left panel and map the new login to a database and give them the desired roles, click OK and you're done.

With all of that said there are two things you should be aware of.

1. Questions like this are better asked on http://serverfault.com/ or apparently http://dba.stackexchange.com :-)
2. It is still better to create windows / domain accounts and either run your application pool under that context or use impersonation.

Answer 2

There are 2 options on the Create/Edit Login dialog, Windows Authentication & SQL Server Authentication, just choose the latter which is the vanilla username + password authentication with no active directory integration.

(Also see Application Roles http://msdn.microsoft.com/en-us/library/ms190998.aspx)

Answer 3

You need to use SQL server authentication rather than windows authentication. See http://msdn.microsoft.com/en-us/aa337562.aspx for details of how to create logins in SQL server