sql 创建某一用户只能查询访问某些表或视图

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/yucaoye/article/details/48004469
use Charges
GO
----------删除-架构-角色-用户-----------------------------------------------
DROP SCHEMA [YXUSER]
GO


DROP SCHEMA [YX_VIEW]
GO

DROP USER [YXUSER]
GO


DROP ROLE [YX_VIEW]
GO


DROP LOGIN [YXUSER]
GO


DROP VIEW [V_YX_StudentChargeDetailItem]
GO


DROP VIEW [V_YX_ChargeItem]

GO

--------------创建视图--------------------

create view b
as
select  *
from a
GO



--建立登录用户:用户名,密码,数据库
exec sp_addlogin 'YXUSER','View123456','Charges'


--创建角色golden
exec sp_addrole 'YX_VIEW'




--授予角色golden 对数据库的select 权限
grant select on V_YX_ChargeItem to YX_VIEW
grant select on V_YX_StudentChargeDetailItem to YX_VIEW


--手动修改用户属性
EXEC sp_grantdbaccess 'YXUSER','YXUSER'
--添加newuser为角色newrole的成员
EXEC sp_addrolemember 'YX_VIEW','YXUSER'
go


展开阅读全文

没有更多推荐了,返回首页