将db_denydatawriter角色分配给用户dbo [英] Assign role of db_denydatawriter to user dbo

问题描述

当我尝试执行此操作时，会收到以下错误消息:

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

DatabaseRole'db_denydatawriter'的添加成员失败. (Microsoft.SqlServer.Smo)
其他信息:
执行Transact-SQL语句或批处理时发生异常. (Microsoft.SqlServer.ConnectionInfo)
无法使用保留的用户或角色名称'dbo'. (Microsoft SQL Server，错误:15405)

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)

我有一个数据库需要保持冻结"状态. 我想保持数据库的所有权，但要防止我自己(以dbo身份)意外修改数据.还有另一种方法可以做到这一点吗?

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?

我决定打开新问题，以解决以下问题:
数据库中的数据需要保持冻结状态.但是，结构可能会更改.

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.

该数据库实际上是几个数据库.它们本质上是每半年获取一次的生产数据库的快照.然后，用户可以通过从前端GUI切换后端数据库来查看历史数据.但是，有时会将新字段添加到当前数据库的表中.如果前端希望存在这些字段，则可能会导致问题.我们当前的解决方案是动态添加字段(用户具有db_datareader，db_denydatawriter和db_ddladmin角色).这意味着不能将数据库设为只读，因为这将阻止对表结构的更改.

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.

推荐答案

将数据库设置为只读:

ALTER DATABASE [db_name]设置为只读

ALTER DATABASE [db_name] SET READ_ONLY

这篇关于将db_denydatawriter角色分配给用户dbo的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！