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.
Examples:
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.