Cannot open User Default Database.
-
2 developers here got this message today "Cannot open User Default Database" while trying to open Enterprise Manager. They didn't have any problem yesterday.
I have Windows Global groups, in Local Groups, and those Local Groups have SQL Logins. They're in the Global group. I had them close EM, re-open, I dropped the users out of the database, deleted the Local Group from SQL & re-added it, set the default databases to Master, took them out of, and back into the Global groups .... Had them close EM again etc ......... but still no-go.
As a workaround I gave them individual SQL logins using their Windows accounts, and everything's fine, but I don't want individual logins.
Can anyone think of what might be screwed up ?
Win2000, SQL2000, SP4
-
I'm really sorry about the multiple posts. I was getting the memory errors on the site just as I was trying to post, and it seems to have put miltiple copies out here.
-
Edit your excess posts and delete em,
Thx
-
Did anything change serverside?
double click the Group in the security folder within enterprise manager.
The first tab indicates the user/group default database. Does the group have access to the db listed? if not change it to a db they do have access to.
-
When creating Active Directory groups to connect to SQL, you might get to the point where you have one user in more than 1 group. You now have one group defaulted to master & maybe another group defaulted to msdb. this user won't be able to log in.
Change the default database of all the logins & users to a database that will always be up, like the master db.
Sit back & relax!! 😉
-
I think that was it ... Thanks !. I set all the group logins to Master & things are back to normal.
-
homebrew01,
it is back to normal only for a short time if everyone will have Master as default database 🙂 Default everyone to Pubs and let them switch to their databases after they connect.
Yelena
Regards,Yelena Varsha
-
Why Pubs instead of Master ?? Because Master is too dangerous to mess with ?
-
You got it right!
The only thing this setup may do is to affect your connections through ODBC sources. If you don't specify the database for the connection then the connection is made to the user's default database. Suppose userA had his default database MyProduction and queries went towards MyProduction automatically. Then you change the default database to Master or Pubs. His queries will go towards Master or Pubs if he does not specify MyProduction explicitly in connection or use a checkbox "Change Default Database To..." in ODBC properties. Moreover, if this userA had permissions to MyProduction and did not have permissions to Master or Pubs (whatever you use for new default) then there will be a message when connecting: "Can not open user's default database...." or something like that.
Yelena
Regards,Yelena Varsha
-
Good points. I found this article about default DB, and the follow-up discussion at: http://www.sqlservercentral.com/columnists/awarren/settingadefaultdatabaseforyourlogins.asp
The consensus seemed to be to either use tempdb as the default, or set up an empty "dummy" db to use as the default. I'm switching from master to tempdb for now.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply