Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com May 24 '12 at 12:51

This question came from our site for professional and enthusiast programmers.

2 Answers 2

up vote 11 down vote accepted

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
share|improve this answer
    
That solved it. Thanks –  Ole Albers May 24 '12 at 12:54

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
share|improve this answer
1  
No. "This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use ALTER DATABASE instead." –  Jon Seigel Jul 28 '12 at 13:30

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.