Setting a Default Database for your Logins

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Andy Warren » Setting a Default Database for your Logins

26 posts, Page 1 of 3

1 2 3 »»»

Setting a Default Database for your Logins

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Warren

Posted Friday, November 29, 2002 12:00 AM

SSCertifiable

Group: Moderators
Last Login: Thursday, May 09, 2013 12:38 PM
Points: 6,462, Visits: 1,384

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
SQLShare - Learn One New Thing Each Day
SQLAndy - My Professional Blog
Connect with me on LinkedIn
Follow me on Twitter

Post #8502

BobAtDBS

Posted Sunday, December 08, 2002 8:45 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 10, 2011 2:00 PM
Points: 297, Visits: 329

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

David.Poole

Posted Monday, December 09, 2002 2:52 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:35 AM
Points: 2,749, Visits: 1,407

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

Post #48107

abhi_develops

Posted Monday, December 09, 2002 2:54 AM

SSC 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

ckempste

Posted Monday, December 09, 2002 3:38 AM

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

Greg Larsen

Posted Monday, December 09, 2002 8:31 AM

Ten Centuries

Group: General Forum Members
Last Login: Saturday, January 19, 2013 8:28 AM
Points: 1,038, Visits: 255

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

joachim.verhagen

Posted Monday, December 09, 2002 8:36 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, May 03, 2013 7:26 AM
Points: 224, Visits: 118

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

sorgan

Posted Monday, December 09, 2002 11:19 AM

Old 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

gljjr

Posted Monday, December 09, 2002 12:52 PM

Mr 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

BobAtDBS

Posted Monday, December 09, 2002 1:31 PM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 10, 2011 2:00 PM
Points: 297, Visits: 329

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 »

26 posts, Page 1 of 3

1 2 3 »»»

Permissions