RESTRICTED_USER

sql sql-server tsql sql-server-administration
pkario picture pkario · May 22, 2009 · Viewed 10.7k times · Source

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?

Answer

John Sansom picture John Sansom · May 22, 2009

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.

Related questions

SQL Batched Delete

I have a table in SQL Server 2005 which has approx 4 billion rows in it. I need to delete approximately 2 billion of these rows. If I try and do it in a single transaction, the transaction log fills up and it …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More