October 27, 2005 at 6:00 am
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
October 27, 2005 at 9:00 am
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
October 27, 2005 at 10:23 am
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
October 27, 2005 at 10:50 am
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?
October 28, 2005 at 2:44 am
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
October 28, 2005 at 3:54 am
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
October 28, 2005 at 7:41 am
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?
October 28, 2005 at 8:18 am
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
October 28, 2005 at 8:31 am
Did you drop the login as well?
October 28, 2005 at 10:40 am
Hi,
in the DSN, did you configure "change default database to 'LDF_Consult'"?
regards
karl
Best regards
karl
October 31, 2005 at 2:51 am
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
October 31, 2005 at 2:55 am
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
October 31, 2005 at 3:27 am
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
October 31, 2005 at 3:42 am
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
October 31, 2005 at 3:54 am
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