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

Single User mode for dbs. Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 5:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,755, Visits: 3,164
I am trying to make a database can only be accessed by myself but not other users for some server migration purpose.

I would like to know when I setup a server to Single -user only, what is exactly is the single user, is it whoever to change the option to single-user?

I just want to make sure I am not accidently doing something no user even myself can ever access the database any more.

Thanks
Post #1375783
Posted Monday, October 22, 2012 5:59 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:27 PM
Points: 219, Visits: 688
Limitations and Restrictions

If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

Prerequisites

Before you set the database to SINGLE_USER, verify that the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When this option is set to ON, the background thread that is used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode.
Post #1375790
Posted Tuesday, October 23, 2012 10:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:53 PM
Points: 1,755, Visits: 3,164
Mac1986 (10/22/2012)[/b]
Limitations and Restrictions

If other users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.


That is fine in our case.

The database remains in single-user mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

Prerequisites


So, if the other user log on, then I as an adminstrator cannot log on, what to do for me to kick the user off, and relogin by myself?
Post #1376163
Posted Tuesday, October 23, 2012 11:08 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 42,484, Visits: 35,553
KILL <user's session ID>
USE <database in single user mode>

or set it to restricted user first, then single user once all the non-admins are out.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1376179
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse