SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Setting a Default Database for your Logins

By Andy Warren,

One of many issues I ended up discussing at PASS 2002 was the issue of setting a default database for logins. Thought it might be worth throwing my ideas on this out for comment!

Just as a brief review, when you add a login to SQL you can set a default database. It defaults to master, but I find most people set it to the database where the login will be used. The advantage to this method is that when you connect using Query Analyzer the db context is already set, no need to find the db in the dropdown or to issue a use statement. It also allows a developer to open a connection without specifying a database in the connection string.

I find that advantage heavily outweighed by the disadvantages. Before I continue....can you think of disadvantages to this technique?

The first is that it's very common for a sql login to exist in multiple databases. Ditto for NT logins. Once it exists in more than one place, which should truly be the 'default' database? If you can make the case that you'll always be using one db for the initial connection, then querying other db's from there it still works. If not, some percentage of the time it's wrong. Using QA it's just a minor inconvenience, for connection strings if the same login will be connecting to different db's they'll have to specify which one each time anyway.


No default db
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=EG\ONE

Default db is logintest
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=logintest;Data Source=EG\ONE

I'd argue that you should ALWAYS set the dbname in the connect string. Otherwise if/when the default db changes in sysxlogins, the application will break...or at least you'll get unusual results! Why would the default db change? One reason is the sysadmin decided to 'neaten' things up, or just changed something inadvertently. Not common of course, but possible. Another reason is if you rebuild a server from backup (or drop a db, then reattach a copy of the mdf) you'll get a different database ID...which is what is stored in sysxlogins. Now the application may break or behave incorrectly, depending on what the new database id points to. Yes, it has happened to me! Easy to fix, just run an update query, but you have to remember to do it.

Another point is that I don't want to keep track of which db each login defaults to. Laziness perhaps, I like to think of it as another step on the road to zero administration!

So, what do I recommend? Set the default db to master and leave it there. Even if you rebuild the server the database ID will be the same. Forces the developers to always specify the dbname in their connection. Now even if someone changes the default db the app should function normally, though it will still affect QA. I like QA always defaulting to master, makes me think for a second about which db I need to be in and make the explicit change.

And the downside to my recommendation? You need to tighten up access in master. Take a look at the permissions granted to public and start removing them. If you don't then potentially a user could browse syscomments and maybe learn something from the code stored there. This is a good step to take regardless of how you decide to handle default logins.

Agree or disagree? Post your comments, I normally reply the same day.

Total article views: 9356 | Views in the last 30 days: 2
Related Articles

Default database

What? The default database is one of the options when creating a login in SQL Server. This is the...


Default Database Drive Almost Full

Change Default Database Location


SQL Server – Login Failed, cannot open user default database

Each login in SQL Server has a default database associated with it. When you login to SQL Server con...


Changing the default port numbers when configuring database mirroring

There are times when you need to use different network ports to communicate between two servers runn...


How to change Default Database locations without a restart

How to change Default Database locations without a restart

sql server 7