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

将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