1 --创建用户 2 USE [UserTest] 3 GO 4 CREATE LOGIN [sa1] WITH PASSWORD=N'123456', DEFAULT_DATABASE=[UserTest], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF 5 GO 6 CREATE USER [sa1] FOR LOGIN [sa1] 7 GO 8 9 --赋予查询权限 10 GRANT SELECT (UserId, UserName,[PassWord]) ON [UserTest].[dbo].[UserInfo] TO sa1 11 12 --赋予修改权限 13 GRANT UPDATE(UserName, [PassWord]) ON [UserTest].dbo.UserInfo TO sa1; 14 15 --下面脚本查看某用户所操作的权限 16 SELECT dp.grantee_principal_id , 17 P.name AS UName , 18 dp.permission_name , 19 C.name , 20 OBJECT_NAME(O.object_id) AS TabName 21 FROM sys.database_permissions dp 22 INNER JOIN sys.objects O ON dp.major_id = O.object_id 23 INNER JOIN sys.columns C ON C.object_id = O.object_id 24 AND C.column_id = dp.minor_id 25 INNER JOIN sys.database_principals P ON P.principal_id = dp.grantee_principal_id 26 WHERE P.name='sa1'