关于SQL Server用户权限控制 

 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'

 

posted @ 2017-08-20 12:23  超锅锅  阅读(293)  评论(0)    收藏  举报