Post reply

Default database to null?

  • August 16, 2005 at 6:21 pm

    #94592

    Can we set default database to null?

    I tried sp_defaultdb 'name' , null and it doesn't work...

    Anyone can help?

  • August 16, 2005 at 7:13 pm

    #582670

    san - what exactly are you hoping to accomplish ?!

    sp_defaultdb is used along with sp_grantdbaccess to grant users access/permissions to a default database - this default database can only be what you have on your server - eg: any databases that you have created/master db/pubs/northwind etc...

    if you explain what you want to do, maybe someone can help you out!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 16, 2005 at 7:20 pm

    #582671

    Hi sushila,

    Actually our developer wants to set the default database to null, as they're saying that make it to specific database, make the users not able to login or to the wrong database.

    We do  pretty much backup and restore in our development server and everytime doing that, we drop and recreate the users , which can point to diferent database everytime there's a request to restore..

    Is it possible then to set to no default database?

    One more thing, is drop and recreate the users is necessary after restoring ?

     

    thx

  • August 16, 2005 at 7:34 pm

    #582675

    san - here's something from BOL that you can pass on to your developers...

    "After sp_defaultdb is executed, the login is connected to the new database the next time the user logs in. However, sp_defaultdb does not automatically give the login access to that database. The database owner (dbo) must give database access to the login through sp_grantdbaccess, or there must be a guest user specified in the database."

    when no default is specified the default becomes the master db which is a no-no since you don't want anyone accidentally modifying anything in this db.

    as for dropping and recreating users after a restore - I've never had to do this...but then I have very small databases and a very small user group..if you've been having problems with logins disappearing after a restore, you may want to search the "Backup" forum for some answers!







    **ASCII stupid question, get a stupid ANSI !!!**

  • August 17, 2005 at 6:38 am

    #582729

    There is another reason not to do this. If a user does not have a default database then they are unable to login to SQl. Period. Does not matter what other databases the login has access to...

    Try it for yourself:

    Create a new database (eg Test)

    Create a new user and grant it access to the above database. Set the default database to be the same. Grant access to any other database as well.

    Open QA and login using the above account to confirm it all works. No surprises here!!

    Now, delete the database created above. In EM the login now shows up with no default database listed.

    In QA attempt the previous connection again. It will fail with "Cannot open user default database. Login failed"

    I would tell the developer to concentrate on coding and let you do the SQL administration!!

    On a side note having no default database will achieve one of the developers aims - no ability to log on.

  • August 17, 2005 at 9:27 am

    #582783

    If a user could have a NULL default database, then why bother having the user in SQL at all ?  If they have a login account, then that assumes they need some sort of access to SQL.  I suppose you could create a dummy database called "DefaultDB" that doesn't contain anything, and set that to be their default DB.

  • August 17, 2005 at 9:42 am

    #582793

    Ok guys thanx a lot for the answers.

     

Viewing 7 posts - 1 through 6 (of 6 total)

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