October 22, 2012 at 5:10 pm
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
October 22, 2012 at 5:59 pm
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.
October 23, 2012 at 10:10 am
Mac1986 (10/22/2012)[/b]
Limitations and RestrictionsIf 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?
October 23, 2012 at 11:08 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply