Setting a Default Database for your Logins

  • Andy Warren

    SSC Guru

    Points: 119676

  • BobAtDBS

    SSCarpal Tunnel

    Points: 4583

    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

  • David.Poole

    SSC Guru

    Points: 75262

    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.

  • abhi_develops

    Say Hey Kid

    Points: 676

    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.

  • ckempste

    SSCoach

    Points: 17983

    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"

  • Greg Larsen

    SSC-Insane

    Points: 20635

    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

  • joachim.verhagen

    Hall of Fame

    Points: 3558

    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.

  • sorgan

    SSC-Addicted

    Points: 429

    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

  • gljjr

    SSCrazy Eights

    Points: 9970

    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.

  • BobAtDBS

    SSCarpal Tunnel

    Points: 4583

    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

  • Andy Warren

    SSC Guru

    Points: 119676

    Setting the default to tempdb or a dummy db are both good ideas. Thanks for the comments so far!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Russell Bride

    SSC Enthusiast

    Points: 183

    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

  • Steven.

    SSCrazy Eights

    Points: 9806

    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

  • Steven.

    SSCrazy Eights

    Points: 9806

    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

  • David Benoit

    SSC-Dedicated

    Points: 34562

    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 26 total)

You must be logged in to reply to this topic. Login to reply