Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

migrated from stackoverflow.com Feb 21 '12 at 12:59

This question came from our site for professional and enthusiast programmers.

3 Answers

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.
share|improve this answer
    
I wasn't aware of serverfault, sorry! In what way are Windows accounts better? Thanks! –  James Feb 21 '12 at 12:20
    
Because they can be used for ntfs security as well as authentication to wcf or web services and other server resources along with being able to login to SQL Server. I am mostly speaking in the context of web applications from experience, is that what you're writing? –  W.Jackson Feb 21 '12 at 12:35
    
+1 Much better practice to use Windows accounts. –  RobS Feb 21 '12 at 12:51
    
+1. Good tip on the benefit of windows accounts. Also, the application can also be used to access the DB with windows accounts. –  StanleyJohns Feb 22 '12 at 6:16
add comment

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)

share|improve this answer
1  
Only if mixed mode autentication is enabled for the server, in recent releases mixed mode ships OFF by default, so you have to enable it and restart the instance first. –  RobS Feb 21 '12 at 12:50
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.