-- 腳本新建登錄資料庫的用戶 USE [master]GOCREATE LOGIN [sa1] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFFGOUSE [yipo ...
-- 腳本新建登錄資料庫的用戶
USE [master]
GO
CREATE LOGIN [sa1] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [yipond0229]
GO
CREATE USER [sa1] FOR LOGIN [sa1]
GO
--給新建的用戶sa1 授權對 某庫某表的 某些欄位進行Select,註意該用戶登錄進之後,只能查看到被授權的表,但可以查看到這些表其他未被授權的欄位
GRANT SELECT (UserSourceTypeId, Token) ON [Yisheng0229].[dbo].[AuthToken] TO sa1
----給新建的用戶sa1 授權對 某庫某表的 某些欄位進行Update,註意該用戶登錄進之後,只能查看到被授權的表,但可以查看到這些表其他未被授權的欄位
GRANT UPDATE([UserSourceTypeId], [Token]) ON [yipond0229].dbo.AuthToken TO sa1;
--下麵腳本查看某用戶所操作的許可權
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
WHERE P.name='sa1'
--關於Delete 和Insert則不能進行列級別的許可權控制,因為它們操作的最小單位是一條記錄