Solved

SQL logins for Web access

Posted on 2010-11-14
13
374 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
  • 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 76

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
 
LVL 76

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 76

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 76

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now