Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Set Database to Single user Expand / Collapse
Posted Monday, March 24, 2008 8:14 PM


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.


"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



Group: General Forum Members
Last Login: Yesterday @ 2:41 PM
Points: 42,081, Visits: 39,473
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...


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

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

Add to briefcase

Permissions Expand / Collapse