THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

When a user logs on to SQL Server, they receive the error “Cannot open user default database. Login failed.” What’s wrong?

Good Question! This is one of those error messages that you will eventually encounter in SQL Server if you work with it enough. A login’s default database is a delicate thing that will prevent them from being able to logon if there is a problem. The default database defines which database the user will be automatically logged into if they connect and they don’t specify a database. Several things can cause the above error message. The most common is a login that does not have access to the database that is configured as its default. This is easily remedied; either set up a corresponding user for the login in their default database or change the default to a database to which the login has access. This error will also rear its ugly head if you drop a database that was configured as the user’s default database. There is no check when a database is dropped to make sure it won’t affect users, so dropping someone’s default database will leave them in the dark when it comes to logging in. As I said, this only happens if the user’s connection method does not specify a database. Many applications will provide the database name and will be immune to the issue. The fix is simple, make sure the logon has access to their default database and make sure the default database exists.

This problem can get a little worse if you break the default database of your system administrator accounts, such as your Windows account that has ‘sa’ level rights or the ‘sa’ login itself (and no other logins have the permissions to fix the problem). In this case, the fix is still simple, but you need to use an application that allows you to specify a database, such as SQLCMD. SQLCMD is a command-line tool that comes with SQL Server 2005 and 2008. When you login, you can specify a database to use that will override the default. So to fix your problem, login with SQLCMD as follows:

sqlcmd -S sql2008 –U sa -P password -d master

This will let you login and use the master database despite the incorrect default database. To fix your login simply run the following query from the SQLCMD command prompt (you can use any database that exists and to which the login has access, master is used as an example):

ALTER LOGIN sa WITH DEFAULT_DATABASE=master

That’s it, the next time you login your new default database of master, or whatever you specified, will be used.

Published Monday, October 27, 2008 10:47 PM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Alexander Kuznetsov said:

Eric,

I guess it would be safer to use model instead of master.

October 28, 2008 9:54 AM
 

ejohnson2010 said:

The problem with using model is that by default, users won't have access to the model database and you will still get a log in failure.   If you were to change that default by giving public access to model, you would be opening a huge security hole as all new user databases from that point forward would inherit that permission. Master is really the only database that you know everyone has access to, and unless you change something about security in master, there is really no harm that can come of making a user's default DB master.

October 28, 2008 10:38 AM
 

Mr_Bill said:

We are hosting some of our sites with an outside hosting company. Under the 2005 Enterprise manager I can get in to their SQL Server and get to our databases (among the hundred that don't belong to us). Under the 2008 Enterprise Manager I am getting a login error because it is trying to login to databases that don't belong to us when doing a database list refresh (although this is not the case under 2005). This happens even when I specify one of our databases as the default database. I'd say this is more of a major problem then most realize when your databases are hosted by hosting companies outside of your organization. I'm trying to get my boss to upgrade our interal systems to SQL 2008 but this sort of oversight is not going to sell him on it.

October 28, 2008 10:47 PM
 

simon said:

Another solution is to have as a standard a small database in each SQL Instance, called, for example, "LoginDB", and have a convention to make *this* the Default Database for *all* Logins. The database can be simple, and could be used for an Auditing \ History Table of SQL Server access via Login Triggers (so you can identify Logins that haven't logged in for 6 months and drop unused Logins, get details of Individual Windows Accounts when only Windows Groups setup as Logins, etc).

This is another way to get around the issue, which can also occur on Mirrored databases where a Database becomes inaccessable following a failover (Role Reversal) - e.g. User has access to a Database on Principal Server, which allows Read access to the equivalent Snapshot Reporting DB on a Mirror Server. After switching Roles and creating new Snapshots off Mirrors, the User cannot access Snapshot DB on the former Principal, due to the main DB now being Mirrored and inaccessable!

October 30, 2008 8:11 AM
 

AaronBertrand said:

October 30, 2008 5:54 PM

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement