Microsoft KB Archive/911847

= The user access option of a SQL Server 2005 database may unexpectedly be set to SINGLE_USER when you restore the database by using a backup file =

Article ID: 911847

Article Last Modified on 12/29/2005

-

APPLIES TO


 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Workgroup Edition

-



Bug #: 402384 (SQL BUDT)

Bug #: 44488 (Content Maintenance)



SYMPTOMS
Consider the following scenario. In a Microsoft SQL Server 2005 database, you change the user access option from MULTI_USER to SINGLE_USER, and then set the database to read-only. Then, you change the user access option back to MULTI_USER. When you back up the database and then restore it by using the backup file, the user access option of the database may still be set to SINGLE_USER.



CAUSE
This problem may occur if the user access option of the database is incorrectly set to SINGLE_USER when you restore the database.



WORKAROUND
To work around this problem, manually set the user access option of the database back to MULTI_USER after you use the backup file to restore the database.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.



MORE INFORMATION
To determine whether the user access option of the database is set to SINGLE_USER, run the following Transact-SQL statement against the database by using SQL Server Management Studio. Select is_read_only as ReadOnly, user_access_desc as UserAccess From sys.databases Where name='DatabaseName' Note is a placeholder for the name of the database.

When you run the Transact-SQL statement, you may receive a result that is similar to the following: ReadOnly UserAccess --- 1        SINGLE_USER If the value in the ReadOnly column is 1, the database is set to read-only.

