November 29, 2002 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/settingadefaultdatabaseforyourlogins.asp>http://www.sqlservercentral.com/columnists/awarren/settingadefaultdatabaseforyourlogins.asp
December 8, 2002 at 8:45 pm
I like the idea of always specifying a default database in the connection string. I'm guilty of not always doing it and your article makes me resolve to start. However, and his may be specific to our applications, but a given application usually only starts up in one database and we make the initial connection there. We usually let the application have a login specific to it and then validate users against a table in that database. Therefore, a given SQL login only has rights to one database in the first place, so to me it makes sense to have the default database be the one it's going to be using.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
December 9, 2002 at 2:52 am
The last database on earth that I want to default to is master!
If you are going to default to a system database then I would make it TempDB. At least if someone does something daft - and that includes DBA's, you can always do a quick restart.
I agree that developers should always be explicit in requesting what they require rather than rely on a property over which they have no control and could potentially be dynamic.
December 9, 2002 at 2:54 am
I agree with Andy, that the database should be a part of the connection string. This is the system that I have followed for most of my projects.
However in case of connection to Oracle, the syntax of the connection string changes. The default tablespace is determined by the loginid used to connect to the database.
The application that I have developed works with both the database 'Oracle as well as SQL Server. TO follow a similar syntax in the code, we have removed the default database from the connection.
December 9, 2002 at 3:38 am
Hi
I thought the DB name was a basic fundamental for developers! id be cracking some heads (some aussie slang ) if they didnt specify it and relied on the default db for the login. Another classic case is when moving (renaming) dbs around which can alter the default db for the login, relying on the default db can screw things up if the dba isnt careful.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
December 9, 2002 at 8:31 am
I used to think I didn't want my logon or SA associated with MASTER, until I tired to recover my server one day a year or so ago.
Can't remember the full details, but if SA defaults to some non-system database, then when restoring MASTER you are going to have a to deal with the issue of SA's defaulting to a database that does not exist.
Because of this recovery issue, I now set the default database for all SYSADMIN accounts to MASTER.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 9, 2002 at 8:36 am
I used to have the problem that if someone logged in and had no permission on the database, that they were moved to the default database without noticing. This results could be unpleasant.
If it was a similar database it could take some time before they found out.
And sometimes they tried to "repair" the master database, if that was the default database.
Therefore I created a nothing database as default database for all users.
It had one table with one field, which told people they were at the wrong place.
Of course everyone had only select permission on that table and nothing else.
That works fine.
Joachim.
December 9, 2002 at 11:19 am
I do the same thing as Joachim. I've created a holding database as the default, because I don't want people to end up in master by default. Works well for me, and avoids some potential unpleasant surprises.
Shawn Organ
Shawn Organ
December 9, 2002 at 12:52 pm
Like Greg I've been bitten by having my account set to a db other than master. I had a problem with a restore of my database and had the server lock up. When I got the server rebooted the db was in Loading status and since it was my default db I was unable to connect to SQL Server. I now have all sysadmin type accounts set to have master as the default database.
Gary Johnson
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
December 9, 2002 at 1:31 pm
Sounds to me after reviewing all the posts, that the smartest strategy would be to have at least one Login (use SA or name one MasterLogin???) defaulted to the Master database for restore issues, have application type logins defaulted to their only database, and have all people oriented user type logins defaulted to a dummy. That's going to be my new strategy unless I hear a good reason why not. I still think the original idea that Andy put forth that each application's Connection String should specify the database to connect to is a sound one.
Student of SQL and Golf, Master of Neither
Student of SQL and Golf, Master of Neither
December 9, 2002 at 4:50 pm
Setting the default to tempdb or a dummy db are both good ideas. Thanks for the comments so far!
Andy
December 10, 2002 at 7:25 am
quote:
The last database on earth that I want to default to is master!If you are going to default to a system database then I would make it TempDB.
This is also what we do, we always make sure that the database is explicitly stated during the connect as we usually have several databases that are used on the one server.
Russell Bride
December 10, 2002 at 7:37 am
I personally set the default database to the primary database that account uses (or the first one they get setup against) as I don't like seeing user accounts in the master database (it makes me wonder what they are doing).
Also connection strings should specify the database.
But I have found two slight drawbacks with setting the initial database.
1. If you are restoring that database, and the application does not specify the connection database the users cannot get in.
2. If you rename that database say in test prior to restoring the current one, the default moves as well (as it is keyed on the DB id)
Steven
December 10, 2002 at 7:42 am
Better add to the first point (as it doesn't read to well)
If the users have access to multiple databases on the same server, and the initial database is offline (dbo mode/restore etc) for some reason, the users cannot get into the other databases if the connection string is not specified.
Steven
December 10, 2002 at 8:48 am
Andy - Good article on a topic most people don't think is important.
I have to agree with steven_white40 "If the users have access to multiple databases on the same server, and the initial database is offline (dbo mode/restore etc) for some reason, the users cannot get into the other databases if the connection string is not specified."
Have had this happen in my environment and there is nothing worse than trying to recover a database and having many customers call me to tell me they can't get in because the database that they connect to as default is offline and therefore they can't do any work against the other db's as well.
Have to admit, I like the dummyDB idea. Thanks for the article and the comments.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply