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?

score 4 · Accepted Answer · answered May 22 '09 at 08:53

4

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.

answered May 22 '09 at 08:53

John Sansom

41,005
9
72
84

that means the regular users remain connected to the server but have no access to restricted db? – pkario May 22 '09 at 09:08
Users are disconnected from the specific database. Access to other databases is available as normal. – John Sansom May 22 '09 at 09:13

RESTRICTED_USER

1 Answers1