Post reply

Set Database to Single user

  • March 24, 2008 at 8:14 pm

    #204660

    Comments posted to this topic are about the item Set Database to Single user

  • July 13, 2012 at 6:20 am

    #1512492

    Forgive my ignorance but...

    The MSDN document was a little vague on who could do this:

    Requires ALTER permission on the database.

    But I know that there are some commands that can alter the database,

    under db_owner but some items require sysadmin (modify file).

    It appears that this can this be done by members of db_owner by default or is this reserved for the sysadmin? If db_owner members can do this then what would we need to change to prevent it or at least limit who can do this? I tried this under a db_owner account on a test database.

    Sorry, I'm just a programmer that sometimes has to wear a DBA hat (more and more) for some DBA actions. I document what I do and tell the DBA's what I plan on doing (or did) and provide the script.

    Thanks.

    "There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker

  • July 13, 2012 at 11:39 am

    #1512669

    An extreme warning needs to be added to this. If you set the database to single user from SSMS, then only THAT session can do anything with the database. You can't even use the explorer window because that's a different session. But, that's NOT the worst of it. If you make the mistake of closing the session that set the DB to single user, you stand a huge chance of a Web Service other some other functionality grabbing that single connection and you won't be able to change it without killing EVERYTHING that touches the database. If you can't find all of that stuff, then it may require a reboot in the "maintenance" mode so that you can actually fix it.

    So, here's the warning...

    [font="Arial Black"]DO NOT CLOSE THE CURRENT SESSION IF IT IS THE SESSION THAT SET THE SINGLE USER MODE!!![/font]

    😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

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