I was just reviewing SQL Server security, and I saw that they recommended that you do not leave the default database of Master; that you should change it to the database that the user or application will need. However, I have rarely seen a user or application that used only one database. Even if you initially did in short time that will change. Though I can see why you should not use Master, what do you do when the user or application is going to use multiple databases?
Thank you
I have also seen situations where logins defaulted to database XXX and then that database was dropped / renamed / moved somewhere else and the login then had no database to connect to. That used to be a problem (user could not login at all), but maybe that has changed (e.g. logins switch to, say, MASTER if the default database is not present?) in recent SQL versions?
Bu what I really want to know is that since any user or application is going to use multiple database, I believe the only thing that you could do is set them to Master to cover them all that may be used. is this right?
Or is there something else that you can do?
Thank you
You can either set the default database to master ... or something else
I expect the Pros / Cons have been debated somewhere online. I know we used to set the default to the most appropriate database on the assumption that if someone just connected and started typing SQL commands (without changing DB) that would at least be "safe"
Then we figured that if that database was removed / renamed they wouldn't be able to log in at all ...
... but your call at the end of the day ...
Everytime I have set a login to a database other than master I regretted it. It caused me pain. Again and again.
Do you have a link to this suggestion? Do they have a reason or specific vulnerability?
Hi, here is the link to one of them, but I have seen this before. I ma just wondering if I am understanding what this actually means.
Thank you
You can set the default database to master or the specific one your application/login will be using, if you are only using one database constantly (as in the case of a service account for a dedicated one DB application) then setting the default DB to the correct one is sensible as all your connections from that account will automatically go to the database it needs to.
If on the other hand, as you have eluded to, your login needs to access multiple databases (on the same instance) then it is actually better for performance to make the master database the default as the login will need to connect to the master database to determine which databases it has access to, kind of makes sense to get that information on hand at the start.
To be fair though, on high performance systems you are not going to see much of a performance difference either way I suspect.