How to add users to SQL Server 2008 Management Studio

Question

I am trying to install DotNetNuke CMS. I created a database in my Microsoft SQL Server Management Studio 2008, which I installed after installing Microsoft Visual Studio 2010. I created a login for it and a password but I am not able to log in.

It is giving me SQL login failed 18456. I have tried the post that exist in this site and many different sites but I am not able to log in.

Do I need to update to SQL Server 2008?

Answer 1

Firstly you need to change the type of login to allow sql security login (otherwise you have to start messing with Domain user accounts).

To do this go to servername --> right click 'properties' --> go to security tab on left hand side --> choose 'Sql Server and Windows Authentication mode'

Then.....

To set up users you need to set them up:

1) as a login 2) as a user in the database.

To do this go to server name (in Management Studio) --> security --> logins then right click 'new login'.

Then go to databases --> your db --> security --> users --> add user.

Then you just need to add the user selecting the elipses and adding the user you just created above.

Answer 2

A few things.

One you'll need to verify that the user you created has the ability to log on. Check the properties inside Sql Server Management Studio. To do this, connect to the database in SSMS. Then find the Security folder. Expand it, and expand the Logins folder. Find the login you are trying to use, right click and choose properties.

On the left is a tree "General", "Server Roles", "User Mapping", "Securables" and "Status". In status make sure that "Permission to connect to database engine" is set to Grant and Login is enabled.

If that is set up that way and you're still having problems, verify that the protocol (either named pipes or TCPIP) is enabled for the server, if this is disabled it will prevent you from making connections from other computers.

edit sounds like you don't have mixed mode authentication turned on.

In SSMS, right click on the server name and go to properties.

Go to the "Security" item on the left.

There are 2 radio buttons at the top: Windows Authentication Mode

and

SQL Server and Windows Authentication mode.

You want to check the 2nd button.