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

Set Database to Single user Expand / Collapse
Author
Message
Posted Monday, March 24, 2008 8:14 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:55 PM
Points: 137, Visits: 251
Comments posted to this topic are about the item Set Database to Single user
Post #473825
Posted Friday, July 13, 2012 6:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 29, 2014 1:06 PM
Points: 30, Visits: 172
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
Post #1329399
Posted Friday, July 13, 2012 11:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 35,589, Visits: 32,178
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...

DO NOT CLOSE THE CURRENT SESSION IF IT IS THE SESSION THAT SET THE SINGLE USER MODE!!!



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1329613
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse