SQLServerCentral / Discuss content posted by Jorge Novo / Article Discussions / Article Discussions by Author / Set Database to Single user / Latest Posts

RE: Set Database to Single user

Jeff Moden — Fri, 13 Jul 2012 11:39:02 GMT

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...[color="RED"][font="Arial Black"]DO NOT CLOSE THE CURRENT SESSION IF IT IS THE SESSION THAT SET THE SINGLE USER MODE!!![/font][/color];-)

RE: Set Database to Single user

charles.byrne — Fri, 13 Jul 2012 06:20:36 GMT

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.

Set Database to Single user

Jorge Novo-@ETLDEVDBA — Mon, 24 Mar 2008 20:14:36 GMT

Comments posted to this topic are about the item [B]Set Database to Single user [/B]