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?

Solution

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.

Comments

[user2820701]

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

[John Sansom]

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