Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

SQL logins for Web access

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
Lorna70
Asked:
Lorna70
  • 5
  • 4
  • 4
4 Solutions
 
javaboonCommented:
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
 
arnoldCommented:
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
 
Lorna70Author Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
arnoldCommented:
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
 
javaboonCommented:
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
 
Lorna70Author Commented:
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
 
javaboonCommented:
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
 
arnoldCommented:
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
 
Lorna70Author Commented:
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
 
Lorna70Author Commented:
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
 
arnoldCommented:
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
 
javaboonCommented:
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
 
Lorna70Author Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now