Stop user access when updating Tables?

Question

Hi,

We are going to run some scripts from consultant to update some tables in a SQL Server Database.

We would like to block end users from accessing that database during our update. Is it feasible ? If not, can we disconnect all users from the database before we perform the update ?

Thanks

Answer 1

You can do one of

ALTER DATABASE TheDb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE TheDb SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE

SINGLE_USER means just one. You can have one single query window open to the database, and you will not have Intellisense available, since it uses a second connection. Also, any services etc will also be thrown out.

RESTRICTED_USER means that only users who are in the db_owner or sysadmiin roles can access the database. This is less restrictive and permits multiple members of the DBA team to connect. Note that if the application against best practice connects as dbo or sa, this option is not going to lock out users.

Once you are done, you open the database with

ALTER DATABASE TheDb SET MULTI_USER