Default database


May 26, 2015 by Kenneth Fisher

The default database is one of the options when creating a login in SQL Server. This is the initial database that the login will connect to when logging in. Unless of course you specify a different database in your connection string.

-- Creating a login with a default database
-- Change the default database of an existing login
ALTER LOGIN [Domain\NewUser] 

The default default database (say that 5 times fast) is of course master.
You can find the default database for each server principal in the default_database_name column of sys.server_principals.

SELECT name, default_database_name 
FROM sys.server_principals

One of the most common problems I see with this is forgetting to create an associated database principal (user) for the default database. This will make it impossible to connect using that login without specifying a database it does have permissions to. The next most common issue I see is when a database has recently dropped but there are still server principals with default databases that point to it.
Typically the only real reason for a default database is convinence. In any connection string I’ve ever worked with you can specify a database to connect to and this overrides the default. That being said people frequently don’t specify a database to connect to. This can be an issue when dealing with applications like Access that will only let you work in the database that you initially connect to (at least as far as I can tell). This means that if the default database changes the connections in an Access database will no longer work.

3 thoughts on “Default database

  1. Nanda Maya Utari says:

    I am interested in your writing . I also have information on the Oracle database and Oracle Course registration you can visit at the

  2. […] connection string or connection options, etc. USE has no effect on the output and neither does your default database. There is also no parameter so this is the only value it will show. Using SQLCMD to make this a bit […]

  3. […] Default database – SQL Studies […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,755 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: