I have two virtual machines. Server A is running SQL Server 2000. Server B is running SQL Server 2008R2. I am using the same domain user to log in to both boxes.

But I cannot log in to SQL Server locally on server A. I get Cannot open user default database. Login failed. when attempting with Query Analyzer or Enterprise Manager. I can connect from server B using SSMS if I specify a database to connect to. Once thus in, I have tried changing the default database for my user to 'master' via the GUI and via exec sp_defaultdb 'DOMAIN\username', 'master' but it does not change things. I have tried setting other databases as the default and verified that those databases are queryable by me. No luck. Unless I open a connection with SSMS and specify a database, the login fails.

This login problem is also preventing me from running linked queries against server A. How can I get this fixed?

  • I take it you aren't using Database Mirroring? – Max Vernon Oct 13 '12 at 4:21
  • Database Mirroring isn't available in SQL Server 2000. – Alexander Garden Oct 18 '12 at 18:15
  • d'oh I only saw the 2008 bit... – Max Vernon Oct 19 '12 at 13:00

That error either means that the default database does not exist or that it is not accessible to the user. Try to set tempdb as the default and see if that helps.

  • No, doesn't work. I have verified that the default database does exist and that I can query its tables after I log in, but I still can't log in without specifying a database. – Alexander Garden Oct 18 '12 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.