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•_ßÏ:_åÉ',
     DEFAULT_DATABASE=[DBLOG], DEFAULT_LANGUAGE=[us_english],
    CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

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

1. 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.

2. 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.

3. 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.

]]>