RESTRICTED_USER

Question

Before changing database schema I issue:

ALTER DATABASE SET RESTRICTED_USER

On completion:

ALTER DATABASE SET MULTI_USER

I understand that a running transaction will be permitted to continue until completion.

Q: Is there any way to wait till all regular users are off the database?

Q: Can the regular users issue more transactions? Can they continue working until disconnected from the server?

Decrepitate

Answer 1

From SQL Server Books Online

http://msdn.microsoft.com/en-us/library/aa933082(SQL.80).aspx

If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely, until the transactions commit or roll back on their own.

So SET RESTRICTED_USER will wait until all transactions have completed before taking affect.

Once in place, regular users cannot issue further transactions, only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles can connect to the database.

Irreparable

that means the regular users remain connected to the server but have no access to restricted db? – Decrepitate

Users are disconnected from the specific database. Access to other databases is available as normal. – Irreparable