Personally I like to set the default database for all logins to tempdb. It helps people remember to select the correct database before they start doing things. If someone forgets to change their database, i’d rather them attempting to do crazy things in tempdb instead of master.

There are some programs / install scripts that don’t handle having a default database that is not master. (Reporting Services install in SQL 2005 SP2 suffered this problem). If your app or install has troubles when the default database is set to tempdb, use SQL Profiler to see if the problem is because the app is expecting the default to be master. If so set the user back to master; run the tasks; and set them back to tempdb as soon as you can.

Set a default database when creating a login in SQL Server 2005:

CREATE LOGIN [MYDOMAINASqlUser]
FROM WINDOWS
WITH DEFAULT_DATABASE = [tempdb]

Change the default database of an existing login in SQL Server 2005 with:

ALTER LOGIN [MYDOMAINASqlUser]
WITH DEFAULT_DATABASE = [tempdb]

To change the default database of an existing login in SQL Server 2000 you would use:

exec sp_defaultdb @loginame = ‘MYDOMAINASqlUser’, @defdb = ‘tempdb’

Tags: SQL Server