Solved

SQL logins for Web access

Posted on 2010-11-14
13
386 Views
Last Modified: 2012-05-10
I'm a SQL newbie and so don't know how to set up logins for a website to connect with SQL Server (Express).  I have so far set up a login as lornawheel in the Master DB (lornawheel is not an administrator) and I have set up another login in my So-Scottish DB and also given that the name lornawheel but mapped it to the Master login.  However, when I try to set up a System DSN, on Servername\SQLExpress using SQL authentication, it gives me the error:

SQL Server Error: 4064...Cannot open user default database. Login failed.

I have checked that SQL server is set to use Windows and SQL logons.

Everything is on the same server (Windows 2003).  What else do I need to do in SQL and is there a way in SQL to test these logins?  I don't think I want to give lornawheel administrator priveleges because this is the user I want to use in my asp connection string (classic asp).
I need this working urgently for a presentation so any ideas would be greatly appreciated.
0
Comment
Question by:Lorna70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
13 Comments
 
LVL 2

Assisted Solution

by:javaboon
javaboon earned 188 total points
ID: 34130715
You want to give the role db_datareader and db_datawriter to the logins for the particular databases to grant access to the databases.

The error you are receiving states that the login works, but you do not have access to the database that is selected default for that login
0
 
LVL 78

Expert Comment

by:arnold
ID: 34131029
You can use a DSNless connection string within the ASP to eliminate the issue choices you made when setting up the DSN which sounds  as though your DSN is to the wrong database.

0
 

Author Comment

by:Lorna70
ID: 34131203
Thanks Javaboon - however, there must be something wrong with the lornawheel user in my So-Scottish DB because when I go into the properties and select db_datareader and db_datawriter and then click OK, I get:
'Login name must be specified'
but the Login_name is greyed out so you can't assign it (the User name is showing lornawheel though.
I also tried T-SQL and had:
USE So-Scottish
EXEC sp_addrolemember 'dbdatareader', 'lornawheel'
but it says 'could not locate entry in sysdatabases for database 'So'...
I then changed it to USE Master but it then said,
User or role 'lornawheel' does not exist in this database'.
I've checked and lornawheel definitely exists as a SQL Login and a User in the DB.
Where on earth am I going wrong?  there must be something wrong with the database or the logon lornawheel.  I also check the DSN and it is pointing to So-Scottish.
Any ideas would be appreciated as I have to get this up and running as soon as.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 78

Expert Comment

by:arnold
ID: 34131334
You need to look in the database servers security section to see whether the user has been added as an sql or a windows login.
Then properties of the user, you can assign it to the database and the role you want it to have.

Does your database server configured for mixed authentication or windows authentication only?
0
 
LVL 2

Assisted Solution

by:javaboon
javaboon earned 188 total points
ID: 34131411
Check the server security tab, the login there should have a permissions tab, then check the database in question and look at the roles. (not to mistake with the schema's). There you should check the datareader/writer roles
0
 

Author Comment

by:Lorna70
ID: 34131497
I don't think the DB User 'lornawheel' is mapped to the 'lornawheel' DB Login.  When I go into the DB login for lornawheel the Login name is blank but greyed out so I can't map it!  Can I map it to the logon lornawheel by T-SQL and if so, what is the code?
I will need to learn about logins and users as I'm getting more and more confused by this.  However, I need to get this up and running for a presentation as soon as possible and I don't care about security as it is on my laptop and not linked to the web.  Any ideas for simplest DSN connection would also be good.
0
 
LVL 2

Expert Comment

by:javaboon
ID: 34131524
the quickest way is to use the login you also use for the management studio (probably sa?) It is unsafe to do so, but on a laptop for presentation could be acceptable. Undo this as soon as you are finished.

In general: in the database server security tab create a new login, and assign the proper roles to the login for the databases. That should work.
0
 
LVL 78

Expert Comment

by:arnold
ID: 34131577
Is the username you reference deals with a windows login account or is it an sql login account.  You can not use a windows login account in a ASP unless it is the account with which the web server runs or based on the user's login.

i.e. using intergrated SSPI.
When the access to the site is made, the user is prompted to login.  This is the usernama/password which whose rights access to the database will be made.

Could you post a jpeg image of the server management\security section that lists the logins.

0
 

Author Comment

by:Lorna70
ID: 34131679
Thanks Javaboon - I looked at the SA login but it looks like it has been given a password and I don't know what it is.  So I thought I'd use Trusted_Connection in my connection string but it gives the same error: 'The Login failed'.  Any other ideas? - I'm beginning to panic now lol!
0
 

Author Comment

by:Lorna70
ID: 34131692
Arnold:  I'm only using SQL logins - the lornawheel username in the DB doesn't seem to be mapped to the lornawheel login and it won't allow me to map it. I tried to delete the username lornawheel so I could create it from scratch but it is the owner of the database so I cannot do that either.  There is clearly either something wrong with this DB or the logins.  I can connect and view table records no problem in SSMS so no idea what's going on!
0
 
LVL 78

Assisted Solution

by:arnold
arnold earned 62 total points
ID: 34131949
I have not idea what that means.  Is the issue that the DB has been restored from a different database where there is a lornawheel user?

The name is not how the access is referenced, the user SID is the issue.

You would need to either copy the lornawheel create login information and run it on this system. or relabel the  DB to grant the local account access to the database.
i.e.

server1 has user lornawheel password='' SID="dfsdgrtresfsdgregfdbdfgdfddfgdfg"
server2 has user lornawheel passowrd='' SID='tereryrtsdgehrfthrthgtfhjfgjyhgjhkj"

The DB in question is a copy restored from server1 and the lornawheel from server2 while the username lornawheel is reflected it is not validated because of the SID mismatch.


Proces to transfer logins between database servers
http://support.microsoft.com/kb/246133
http://support.microsoft.com/kb/918992 
0
 
LVL 2

Accepted Solution

by:
javaboon earned 188 total points
ID: 34136450
Hi Loma,

what account do you use to look up the permissions in the ssms?

try to change the database owner via sp_changedbowner  (syntax is sp_changedbowner newowner)
then delete the login and recreate.

A screenprint from the ssms could help
0
 

Author Closing Comment

by:Lorna70
ID: 34165502
Thanks everyone and sorry for taking so long to get back but was so busy getting ready for the presentation.  Anyway, yes the DB in question was a restored one so that could have been causing my problems.  I took a shortcut and created a new login and a new username mapped to that login and used that in the DSN and it worked :-)
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question