Cannot connect to user default database

Question

Cannot connect to user default database

andoi

SSC Eights!

Points: 814
More actions
October 27, 2005 at 6:00 am

#188232

I am having a bizare problem...
we are connecting to a database using windows groups & windows authentication.  The connection from the application to the Server is via an ODBC DSN.
When I try to connect to the Database I get "Cannot open User Default Database. Login failed"
I have checked the usual Things like the group having a default database set and I even changed this to another database with the same results.
I have granted all permissions to the public role for this database (just to check for problems).
I have run sp_helprotect and public has select permissions to the tables (as does the group)

I have also run the following script on the database
select cast(object_name([id]) as varchar(40)) [object],
(CASE [action]
WHEN 26 THEN 'REFERENCES'
WHEN 178 THEN 'CREATE FUNCTION'
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 198 THEN 'CREATE TABLE'
WHEN 203 THEN 'CREATE DATABASE'
WHEN 207 THEN 'CREATE VIEW'
WHEN 222 THEN 'CREATE PROCEDURE'
WHEN 224 THEN 'EXECUTE'
WHEN 228 THEN 'BACKUP DATABASE'
WHEN 233 THEN 'CREATE DEFAULT'
WHEN 235 THEN 'BACKUP LOG'
WHEN 236 THEN 'CREATE RULE'
END) [action],
(CASE protecttype
WHEN 204 THEN 'GRANT_W_GRANT'
WHEN 205 THEN 'GRANT'
WHEN 206 THEN 'DENY'
END) protecttype,
columns --only needed in master and reference was altered for better view below.
from sysprotects where uid = 0 order by object
With the following results
object                                   action           protecttype   columns
---------------------------------------- ---------------- ------------- --------
NULL                                     NULL             GRANT         NULL
CommentXDoc                              SELECT           GRANT         0x01
Consultee                                SELECT           GRANT         0x01
ContributorsComments                     SELECT           GRANT         0x01
ExternalAddress                          SELECT           GRANT         0x01
LiveGazView                              SELECT           GRANT         0x01
syscolumns                               SELECT           GRANT         0x01
syscomments                              SELECT           GRANT         0x01
sysconstraints                           SELECT           GRANT         0x01
sysdepends                               SELECT           GRANT         0x01
sysfilegroups                            SELECT           GRANT         0x01
sysfiles                                 SELECT           GRANT         0x01
sysforeignkeys                           SELECT           GRANT         0x01
sysfulltextcatalogs                      SELECT           GRANT         0x01
sysindexes                               SELECT           GRANT         0x01
sysindexkeys                             SELECT           GRANT         0x01
sysmembers                               SELECT           GRANT         0x01
sysobjects                               SELECT           GRANT         0x01
syspermissions                           SELECT           GRANT         0x01
sysprotects                              SELECT           GRANT         0x01
sysreferences                            SELECT           GRANT         0x01
syssegments                              SELECT           GRANT         0x01
systypes                                 SELECT           GRANT         0x01
sysusers                                 SELECT           GRANT         0x01
I am now at a loss as to the cause of the problem

Any ideas as to the cause & solution would be greatly appreciated

Thanks

Andy

Viewing 15 posts - 1 through 15 (of 16 total)

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

Kory Becker Hall of Fame Points: 3565 More actions · Answer 1

Does this group exists as a user in the default database? If it doesn't add it. If it does, is the database user name the same as the login name?

db user login

DOMAIN\kbecker DOMAIN\kbecker

I have seen this when the below is present:

db user login

kbecker DOMAIN\kbecker

andoi SSC Eights! Points: 814 More actions · Answer 2

Thanks for the reply

I have checked the database and yes they are the same

db user Login

Horsham1\LDC_Reports Horsham1\LDC_Reports

Regards

Andy

Kory Becker Hall of Fame Points: 3565 More actions · Answer 3

Can you log into the server and get to the db through EM with user in that group? Is the db in 'single user' or 'dbo use only' mode?

andoi SSC Eights! Points: 814 More actions · Answer 4

Thanks for the reply

The Database is in working order & has other users connected.

I cannot connect as the user with EM as I get the "Cannot open User Default Database. Login failed" Error

Regards

Andy

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

did someone drop and recreate the database ?

Connect using Query Analyser. You'll also get the message, but it will connect to master and you can work from there on.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Kory Becker Hall of Fame Points: 3565 More actions · Answer 6

Does sp_helpdb show restricted options and do the users that can connect have db_owner role in the database. Just something that could be overlooked. If possible can you drop the user and login and then try recreating it?

andoi SSC Eights! Points: 814 More actions · Answer 7

Thanks for the replies

The database has not been dropped and recreated

I cannot connect (as the user) from QA as this also gives the same error

sp_helpdb gides the following

======================

Database LDF_Consult

db_size 277.69 MB

Owner HORSHAM1\IT_BRADLE1

dbid 62

created Feb 11 2005

Status Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=539, Collation=Latin1_General_CI_AS, SQLSortOrder=0, IsTornPageDetectionEnabled, IsAutoCreateStatistics, IsAutoUpdateStatistics

Compatability level 80

===============

So as you can see everything looks OK

I have dropped the user a few times and recreated it with the same results (first thing i tried)

Regards

Andy

Kory Becker Hall of Fame Points: 3565 More actions · Answer 8

Kory Becker

Hall of Fame

Points: 3565

October 28, 2005 at 8:31 am

#600926

Did you drop the login as well?

Karl Klingler SSCertifiable Points: 5869 More actions · Answer 9

Hi,

in the DSN, did you configure "change default database to 'LDF_Consult'"?

regards

karl

Best regards
karl

andoi SSC Eights! Points: 814 More actions · Answer 10

Hi

Thanks for the replies

I may have been a bit vague ...

I have dropped the Logon and User from the database and have recreated them with the same results as before.

When I try to connect via ODBC I do not get as far as selecting the Default database before i get the error message "Cannot open User Default Database. Login failed" - this appears after I have set the login credentials.

I have tried changing the User default database (to another one where all users have permissions) & this makes no difference what so ever.

I have tried dropping the user (& login) & recreating (many times) & this makes no difference.

I hope this makes the situation a bit clearer

Regards

Andy

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 11

can you remove the odbc-dsn and then build it up again ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

andoi SSC Eights! Points: 814 More actions · Answer 12

Hi Thanks for the reply

I cannot rebuild the dsn because I am unable to create it in the first place as I cannot connect to the server to setup the DSN without getting the "Cannot open User Default Database. Login failed" Error.

This is before I get to the screen where I select the database

Regards

Andy

seanaft SSC Journeyman Points: 76 More actions · Answer 13

Sounds more like a permissions problem for your Windows user.

Is it a memeber of the same group where other users are allowed in?

Has it been revoked access to SQLServer perhaps (or not been granted)

Sean

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 14

Are u allowed to use regedit ?

check [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\yourdsn

and remove the "Database"="faultydbname"

or replace it with a correct dbname

or remove the full yourdsn with the subkeys.

As usual the disclaimer is : I'm not responsable for whatever anyone messes up using this stuff.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me