As I understand it the default database is specified so that in case you don't specify one in the connection string, it will know where to start. Is this correct?

I've never seen a connection string without a database specified, is it even possible? Why would anyone want to do this? It just makes it less transparent to which database you are connecting.

In other words is there any reason to choose anything else than master as the default database?


Well Management Studio, for example, by default, only asks for (and only requires) server name and credentials. You can override these if you bother going into Options, but most people don't.

Do you really want all of your users connecting to master? In a lot of shops this can cause quite a bit of headache, especially with loose security. If users have the permission to create things in master, if they connect to master by default, they inevitably will create things there. And this can only mean cleanup will be needed later.

  • 1
    Which is why some (such as Erland Sommarskog) have suggested tempdb as the default database. – RLF Feb 28 '14 at 13:46
  • 1
    @RLF true, but there is no perfect answer. For example, setting the default database to tempdb could still lead to people creating objects there, and not being able to find them later. In fact you could argue that that would be worse, because if SQL Server has restarted, the objects created inadvertently in master will still be there, but the ones in tempdb will not. – Aaron Bertrand Feb 28 '14 at 22:30
  • 1
    True, true. I also played with a few other approaches, but all have their issues. Of course, for non-admins the essential thing is make sure that they cannot create anything in master or msdb. – RLF Feb 28 '14 at 22:36
  • That makes sense. In our shop we have administrators who can do anything and they administer many different systems - setting their default database to master or tempdb would cause least confusion. All other users don't have access to change the schemas, so they can do no harm in master and tempdb. For us either of the two would work and perhaps tempdb is better since it leaves less room for error on the administrators' side. – Martin Ørding-Thomsen Mar 2 '14 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.