Assign role of db_denydatawriter to user dbo
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 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). Wh开发者_如何学编程at this means is that the database cannot be made read-only because that would prevent changes to the table structure.
set the db to read only:
ALTER DATABASE [db_name] SET READ_ONLY
精彩评论