What?

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.

 
How?

-- Creating a login with a default database
CREATE LOGIN [Domain\NewUser] FROM WINDOWS 
WITH DEFAULT_DATABASE = TestDB
-- Change the default database of an existing login
ALTER LOGIN [Domain\NewUser] 
WITH DEFAULT_DATABASE = NewDefaultDB

 
Default

The default default database (say that 5 times fast) is of course master.

 
Where?

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

 
Problems

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.

 
Why?

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.

Filed under: Microsoft SQL Server, Settings, SQLServerPedia Syndication Tagged: database settings, default settings, microsoft sql server