Search code examples
sqlsql-servert-sqlsql-server-administration

RESTRICTED_USER

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.