SQL Server中对列的权限设置
SQL Server中对列的权限设置一、方式一：使用视图
将需要限制用户只能看到特定的几个列、设置成一个视图，然后对这个视图进行权限控制
二、方式二：使用GRANT语句
1、授予相关列的查询权限（SELECT）
(1)、在数据库db1中，登录名UserA 只能有权限查询 Employee表 里面的BusinessEntityID, NationalIDNumber, LoginID三个字段权限，不能查询其它字段
GRANT SELECT(BusinessEntityID, NationalIDNumber, LoginID) ON Employee TO UserA
(2)、可以用下面SQL查看授予UserA的权限
SELECT dp.grantee_principal_id ,
P.name AS UName ,
dp.permission_name ,
C.name ,
OBJECT_NAME(O.object_id) AS TabName
FROM sys.database_permissions dp
INNER JOIN sys.objects O ON dp.major_id = O.object_id
INNER JOIN sys.columns C ON C.object_id = O.object_id
AND C.column_id = dp.minor_id
INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id and P.name='UserA'
(3)、如果查询语句使用BusinessEntityID, NationalIDNumber, LoginID字段之外的其它字段
SELECT BusinessEntityID, NationalIDNumber, LoginID,JobTitle FROM Employee
报如下错误
Msg 230, Level 14, State 1, Line 8
The SELECT permission was denied on the column 'JobTitle' of the object 'Employee', database 'db1', schema 'dbo'
2、授予相关列的修改权限（Update）
(1)、对于登录名UserB，只允许其修改 Address表 的AddressLine1,AddressLine2两个字段，其它字段不许修改
GRANT UPDATE(AddressLine1,AddressLine2) ON Address TO UserB
(2)、可以用下面SQL查看授予UserB的权限
SELECT dp.grantee_principal_id ,
P.name AS UName ,
dp.permission_name ,
C.name ,
OBJECT_NAME(O.object_id) AS TabName
FROM sys.database_permissions dp
INNER JOIN sys.objects O ON dp.major_id = O.object_id
INNER JOIN sys.columns C ON C.object_id = O.object_id
AND C.column_id = dp.minor_id
INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id and P.name='UserB'