Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Single User mode for dbs.
Add to briefcase

Single User mode for dbs. Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
sqlfriends
Posted Monday, October 22, 2012 5:10 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:55 PM
Points: 1,597, Visits: 2,770
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
Mac1986
Posted Monday, October 22, 2012 5:59 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:33 AM
Points: 180, Visits: 512
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
sqlfriends
Posted Tuesday, October 23, 2012 10:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 4:55 PM
Points: 1,597, Visits: 2,770
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
GilaMonster
Posted Tuesday, October 23, 2012 11:08 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 37,640, Visits: 29,895
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.