What permission is needed to set Database read-only?

Question

I am dbowner of a database which I want to set read-only. (through sql management studio). When I try that I get a permission error (297)

Which role is needed to perform that action?

Answer 1

Form Query Analyzer or Management Studio, you can issue the following command:

ALTER DATABASE database-name SET READ_ONLY

As mentioned here, you need the alter database rights and you must set the database to single mode.

Changing the state of a database or filegroup to READ_ONLY or READ_WRITE requires exclusive access to the database.

USE master;
GO
ALTER DATABASE AdventureWorks2012
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE AdventureWorks2012
SET READ_ONLY;
GO
ALTER DATABASE AdventureWorks2012
SET MULTI_USER;
GO

Answer 2

There is a system stored procedure. You can also use this for this task.

> Syntax: sp_dboption [ [ @dbname = ] 'database' ] 
>     [ , [ @optname = ] 'option_name' ] 
>     [ , [ @optvalue = ] 'value' ]  [;]

Example:

 EXEC SP_DBOPTION   -- To check Settable option

 EXEC SP_DBOPTION 'MyDatabase' -- Return the following option that are
 set.

 EXEC SP_DBOPTION 'MyDatabase','READ ONLY' --- Set the Read Only to the db

 EXEC SP_DBOPTION 'MyDatabase','READ ONLY','FALSE' --- Change the
 current setting of the option