Temporarily disable SELECT for one table (during an update)

Question

We have a dedicated data mart SQL Server with several really big tables that are updated one partition at a time via parametrized SSIS loop. Quite often when clients try to read such a table during an update, they get in between ETL loop steps, so the whole table is locked until all the reads are finished, and that goes on and on for hours.

So how do I:

• drop all active SELECT queries for a specific table,
• disable all operations on it for everyone except an ETL account,
• and make it readable again afterwards?

A very crude temporary solution that I came up with is just disabling all the client accounts during the big update time:

DECLARE @queryDisable NVARCHAR(MAX) = N'';

SELECT @queryDisable += 'ALTER LOGIN ' + QUOTENAME(name) + ' DISABLE;' + CHAR(13) + CHAR(10)
FROM syslogins
WHERE name like '%client%'

PRINT @queryDisable;
EXEC sp_executesql @queryDisable;

But that locks them out of the whole server while they may need to access a different database which is not being updated at the time.
I hope there is more elegant and civilized way than bruteforce DENY/GRANT cycling of SELECT permissions on every account there is.

Edit 2022-03-04: Thank you for your answers, they are duly noted and I will report back on progress, but we're making big releases every 1-2 months so it may take some time.

Answer 1

In a transaction ALTER the table. You will get an exclusive schema lock for the duration of the transaction. Requiring a transaction in SSIS may be enough, alternatively set RetainSameConnection on the connection manager and handle the transaction and schema lock in a TSQL step.

You can get the schema lock with any alter table, like ALTER TABLE . . . SWITCH, or with SP_RENAME. A cheap and harmless ALTER TABLE for any table that doesn't have any disabled constraints is something like

alter table SomeTable with nocheck check constraint all

Which is a noop, except that it has the side effect of requiring your transaction to hold an SCH-M lock.

A more intrusive method is to put the database in SINGLE USER mode killing all the other connections.

Answer 2

Sorry for the late entry, but I was testing out the direction that David Browne pointed out — and it works just as needed!

A more intrusive method is to put the database in SINGLE USER mode killing all the other connections.

Turns out that a database Restrict Access option has not only MULTI_USER and SINGLE_USER modes (accessible to everbody/nobody respecitvely) but also a RESTRICTED_USER:

Only members of the db_owner, dbcreator, or sysadmin roles can use the database.

Since our service accounts have those roles they still can perform their ETL duties while clients receive a native MS SQL message that the database is currently in restricted access state. Our managers notified all the clients that this message means the DB is under necessary monthly maintenance and everything will be available as soon as clients receive «Data is updated» email (which is sent automatically as the ETL's last step).

The downside is that changing DB state will force drop all its active connections — no matter what role the user has, so make sure no important queries are going on or wait for them to finish. The upside is that all the other databases are not affected and are available as usual.

GUI path is: Database Properties → Options → State (the last dropdown) → Restrict Access.

You can also do it programmatically:

ALTER DATABASE [Name] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
-- WITH ROLLBACK IMMEDIATE doesn't wait for active queries completion
...
ALTER DATABASE [Name] SET MULTI_USER;

---

Though while this method works for a monthly updated DB, I'm still working out a way to lock specific tables because the other big-tabled DB is updated constantly and RESTRICTED_USER will essentially make it never available for the clients. So I will try to update the answer later if I'll come up with something on that matter.