create table tb (id int, name varchar(20), address varchar(100)); go insert into tb values(1,'Tom','SSt. 100'); gocreate user [usr] without login; go-- 授予 [usr] 帐户对 dbo.tb 的 select 权限 grant select on dbo.tb to [usr]; go -- 授予 [usr] 帐户对 dbo.tb 的 [address] 列的 update 权限 grant update([address]) on dbo.tb to [usr]; go -- 拒绝 [usr] 帐户对 dbo.tb 的 [id] 列的 select 权限 deny select([id]) on dbo.tb to [usr]; go-- 测试 exec as user='usr'; -- 查询 name,[address] 列成功 select name,[address], USER_NAME() username from dbo.tb; -- 更新 [address] 列成功 update dbo.tb set [address]='St. 101' where name='Tom'; -- 查询 id 列失败 select id from dbo.tb; revert; drop table tb; drop user [usr]; go
create table tb (id int, name varchar(20), address varchar(100));
go
insert into tb values(1,'Tom','SSt. 100');
gocreate user [usr] without login;
go-- 授予 [usr] 帐户对 dbo.tb 的 select 权限
grant select on dbo.tb to [usr];
go
-- 授予 [usr] 帐户对 dbo.tb 的 [address] 列的 update 权限
grant update([address]) on dbo.tb to [usr];
go
-- 拒绝 [usr] 帐户对 dbo.tb 的 [id] 列的 select 权限
deny select([id]) on dbo.tb to [usr];
go-- 测试
exec as user='usr';
-- 查询 name,[address] 列成功
select name,[address], USER_NAME() username from dbo.tb;
-- 更新 [address] 列成功
update dbo.tb set [address]='St. 101' where name='Tom';
-- 查询 id 列失败
select id from dbo.tb;
revert;
drop table tb;
drop user [usr];
go