Sql-server – Getting “Cannot open user default database. Login failed.” for connections that don’t specify a default database

sql serversql-server-2000

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?

Best Answer

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.

Related Solutions

SQL Server – Login Failed for User ‘web_user’ on Database ‘user_auth.mdf’

If you created the database using SSMS, you should use the logical database name and not the file with the .mdf extension in your connection string.

Use

Server=XYZ;Database=user_auth;User ID=web_user;Password=web_user

for your connection string and let SQL Server worry about the file-related details.

SQL Server – Create Login and User for Single Schema Access

Off guess you are missing the connect permission. I'd always thought that was created with the user but there may be conditions where it isn't added.

Try running

USE [MyDatabase];
GO
GRANT CONNECT TO [MyDomain\MyAccount];

You could also try changing the default schema of [MyDomain\MyAccount]

ALTER USER [MyDomain\MyAccount] WITH DEFAULT_SCHEMA = MySchema;

Lastly you can use my sp_dbpermissions and sp_srvpermissions to review the permissions for the user.

EXEC sp_srvpermissions 'MyDomain\MyAccount'; -- Return instance level permissions
EXEC sp_dbpermissions 'All','MyDomain\MyAccount'; -- Return DB level permissions for all databases

Best Answer

Related Solutions

SQL Server – Login Failed for User ‘web_user’ on Database ‘user_auth.mdf’

SQL Server – Create Login and User for Single Schema Access

Related Question