Creating Login SQL Express 2008

Question

Creating Login SQL Express 2008

dec_obrien

Default port

Points: 1486
More actions
July 5, 2011 at 8:39 am

#253212

Hi,
I'm new to SQL Server 2008. I'm trying to create a new Login in SQLEXPRESS 2008 so that I can connect to the database. I have created a new database called Test. I have created a new Login with the default database as Test, SQL Authentication. I have created a User in Test called TestLogin and mapped this user to TestLogin login. I have granted Connect permission to master and Test databases but when I try to connect I get SQL Server Error 18456 (SQL State: 28000).
What am I doing wrong?
Thanks.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Brian O'Leary SSChasing Mays Points: 623 More actions · Answer 1

Where is the express instance installed, on you local machine or on a server somewhere? is it a named instance or default? what are you using to connect to it?

MCITP SQL Server 2005/2008 DBA/DBD

dec_obrien Default port Points: 1486 More actions · Answer 2

It's on a server that I remote onto. The Name is (servername)\SQLEXPRESS

I was trying to create a DSN one the server just to test the connectivity.

Peter1971 Old Hand Points: 363 More actions · Answer 3

dec_obrien (7/5/2011)
Hi,
I'm new to SQL Server 2008. I'm trying to create a new Login in SQLEXPRESS 2008 so that I can connect to the database. I have created a new database called Test. I have created a new Login with the default database as Test, SQL Authentication. I have created a User in Test called TestLogin and mapped this user to TestLogin login. I have granted Connect permission to master and Test databases but when I try to connect I get SQL Server Error 18456 (SQL State: 28000).
What am I doing wrong?
Thanks.

In what order did you map the user to the database?

When you create a Login you can use "User Mapping" in the Login properties to map the login to a database.

In this mapping you can also create/set database roles.

Edit: In the server properties you can find (under "Permissions") your Login and you can grant "Connect SQL".

A good description of the problem is half the solution.. ;-)

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 4

First you need to connect locally as an administrator. Local Windows admins usually have default rights, or the account that installed Express.

to add other accounts, use CREATE LOGIN: http://msdn.microsoft.com/en-us/library/ms189751.aspx

dec_obrien Default port Points: 1486 More actions · Answer 5

Hi,

So if I want to add a Login, I first create a User e.g. called TestUser on the database and then:

CREATE LOGIN TestLogin WITH PASSWORD = '<enterStrongPasswordHere>',

CREDENTIAL = TestUser;

GO

or do I create the Login first and then add the User (or does it matter)? http://msdn.microsoft.com/en-us/library/aa337545.aspx

What permissions do I need to grant? Connect? Do I need to grant Connect to master or the other databases? I know how to grant permissions to database objects.

THanks

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 6

Create the login, then the user. I would set the login default database to the one in which you create the user. The login will have connect rights.

Then grant your object permissions.

dec_obrien Default port Points: 1486 More actions · Answer 7

Hi,

Eventually cam eback to this and after some more research, I discovered that the Instance was set to Windows Authentication Mode only and not Windows & SQL Server Authentication!

Fixed anyway, thanks for the help.