Cannot connect to user default database

  • 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

     

  • 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

     

  • 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

  • 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?

  • 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

  • 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

  • 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?

  • 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

  • Did you drop the login as well?

  • Hi,

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

    regards

    karl

    Best regards
    karl

  • 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

  • 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

  • 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

  • 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

  • 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

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

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