Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Setting a Default Database for your Logins Expand / Collapse
Author
Message
Posted Friday, November 29, 2002 12:00 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 11:14 AM
Points: 6,705, Visits: 1,679
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

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #8502
Posted Sunday, December 08, 2002 8:45 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 06, 2013 6:52 AM
Points: 298, Visits: 332
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
Post #48106
Posted Monday, December 09, 2002 2:52 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 2,865, Visits: 1,705
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.




LinkedIn Profile
Newbie on www.simple-talk.com
Post #48107
Posted Monday, December 09, 2002 2:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, October 22, 2009 11:55 AM
Points: 34, Visits: 2

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.





Post #48108
Posted Monday, December 09, 2002 3:38 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
Points: 885, Visits: 1
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"
Post #48109
Posted Monday, December 09, 2002 8:31 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Saturday, April 12, 2014 6:50 AM
Points: 1,040, Visits: 274
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

Need SQL Server Examples check out my website at http://www.sqlserverexamples.com
Post #48110
Posted Monday, December 09, 2002 8:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 9:32 AM
Points: 224, Visits: 125
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.




Post #48111
Posted Monday, December 09, 2002 11:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 24, 2007 11:33 AM
Points: 339, Visits: 3
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
Post #48112
Posted Monday, December 09, 2002 12:52 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, February 03, 2005 3:17 PM
Points: 504, Visits: 1
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.
Post #48113
Posted Monday, December 09, 2002 1:31 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 06, 2013 6:52 AM
Points: 298, Visits: 332
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
Post #48114
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse