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?