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'
