Default database

1

May 26, 2015 by Kenneth Fisher

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.

One thought 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 http://lpug.gunadarma.ac.id/sertifikasi/owdp/eng/welcome

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

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

Join 2,134 other followers

Follow me on Twitter

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