Setting a Default Database for your Logins

,

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.

Rate

5 (3)

Share

Share

Rate

5 (3)