Stop user access when updating Tables?

TonyJK 876 Reputation points
2021-02-17T22:16:10.947+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,079 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 103.5K Reputation points MVP
    2021-02-17T22:25:55.453+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful