将SQLServer单个表设置只读访问权限-526互联

将SQLServer单个表设置只读访问权限

1. Insert,Update,Delete 触发器

CREATE TRIGGER trReadOnly ON tablename
    INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
    RAISERROR( 'table is readonly.', 16, 1 )
    ROLLBACK TRANSACTION
END

2. 拒绝对象级别权限

DENY INSERT, UPDATE, DELETE ON tablename TO dbusername
DENY INSERT, UPDATE, DELETE ON tablename TO Public

3. 创建只读视图

create view V_Table1
as
select ID, field1 from table1
union all
select 0, '0' where 1=0