Default Database Issues and Best Practices

What is Default Database?

For every user, there is an option of
setting a default database. So when that user connects to the SQL Server, they will
be taken to the set default database.

So in the above case, when dba_testuser logs
in, they will be taken to the DBLOG database.

Configuring Default Database

When you create login using a script, this
is the syntax to allocate default database.

    CREATE LOGIN [dba_testuser] WITH PASSWORD=N'_1éÚÕ´2®œ OÙ_{wKmÌ_ÔõIJZ•_ßÏ:_åÉ',



Also, if you want to change only the
default database, this the syntax you need to use.     

EXEC sp_defaultdb @loginame='dba_testuser', @defdb='tempdb'

Issues with Default Database

By default, the master database is the default
database for every login. That is the reason whenever, you log in you will be
taken to the master database. Because of this, you will see lot of unnecessary
database objects in the master database since users will create objects
after login without changing the database. Recently, I found 35 tables and stored procedures, in the
master database.

Since the master database is not the a good option
for the default database,  you can assign a user database for the login. There are however issues with this in cases where the database is dropped or user access is removed from
the database. In these circumstances and error will be generated upon login and you need to change the default
database at login.

You can change the default
database by using Connection Properties tab and changing the Connect to
database option.

The same issue will occur, when restoring a
database. Let us assume that, default database DB1 is set as the default database
for user1. When you need to restore database, it doesn’t allow restoring until
you change the default database of user1. In simple terms, the restoring database cannot
be assigned as default database to any users.

Note that, 2 & 3 issues
are fixed in SQL Server 2012. In case the database is not available or the user is not
permitted to access the default database, the user will be taken to the master
database without any errors.

Best Practices

I would recommend to use tempdb as the
default database. Since tempdb is initialized after every restart of the
SQL Server service, all the unnecessary objects will be dropped. Also, you
won’t run into issues of database is dropped or user doesn’t have access to it.


No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |