2

When I try to do this I receive the following error message:

Add member failed for DatabaseRole 'db_denydatawriter'. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Cannot use the reserved user or role name 'dbo'. (Microsoft SQL Server, Error: 15405)

I have a database that needs to remain 'frozen'. I want to maintain ownership of the database, but prevent myself (as dbo) from accidentally modifying the data. Is there another way to accomplish this?

EDIT: I've decided to open a new question to address the lines below:
The data in the database needs to remain frozen. However, the structure might change.

The database is actually several databases. They are essentially snapshots of the production database taken semi-annually. Users are then able to view historical data by switching the back-end database from the front-end GUI. However, sometimes new fields get added to the tables in the current database. This can cause problems if the front-end expects these fields to be present. Our current solution is to add the fields on the fly (users have db_datareader, db_denydatawriter, and db_ddladmin roles). What this means is that the database cannot be made read-only because that would prevent changes to the table structure.

Community
  • 1
  • 1
mwolfe02
  • 23,787
  • 9
  • 91
  • 161
  • When the current db structure changes, is it a controlled change by the dba or a developer? If so, then just turn off the read only flags, make your changes to the snapshot dbs, and turn the flags back on. – Ray Feb 05 '10 at 17:39
  • I could do that, but the snapshot is large enough that it takes nearly a full minute to switch the flag. Appending the field to a table in the non-read-only db takes at most a couple of seconds. From a performance standpoint, switching the flag off and on is not feasible. – mwolfe02 Feb 05 '10 at 17:57
  • How often does this happen? Is two minutes of down-time on a snapshot a big problem? – Ray Feb 05 '10 at 18:00

1 Answers1

3

set the db to read only:

ALTER DATABASE [db_name] SET READ_ONLY

Ray
  • 21,485
  • 5
  • 48
  • 64