SQL Server服务器级权限

SQL Server 2008共有26个服务器级权限，分别为

1. ADMINISTER BULK OPERATIONS

2. ALTER ANY CONNECTION

3. ALTER ANY CREDENTIAL

4. CREATE ANY DATABASE

5. ALTER ANY DATABASE

6. VIEW ANY DATABASE

7. CREATE ENDPOINT

8. ALTER ANY ENDPOINT

9. CREATE DDL EVENT NOTIFICATION

10. CREATE TRACE EVENT NOTIFICATION

11. ALTER ANY EVENT NOTIFICATION

12. ALTER ANY LINKED SERVER

13. ALTER ANY LOGIN

14. ALTER ANY SERVER AUDIT

15. ALTER RES

16. ALTER TRACE

17. AUTHENTICATE SERVER

18. CONNECT SQL

19. CONTROL SERVER

20. EXTERNAL ACCESS ASSEMBLY

21. SHUTDOWN

22. UNSAFE ASSEMBLY

23. VIEW ANY DEFINITION

24. VIEW SERVER STATE

25. OURCES

26. ALTER SERVER STATE

27. ALTER SETTINGS

SQL Server 2016 中比 2008 多出了如下8个服务级权限

1. CREATE AVAILABILITY GROUP     --SQL Server 2012

2. ALTER ANY AVAILABILITY GROUP    --SQL Server 2012

3. ALTER ANY EVENT SESSION

4. CREATE SERVER ROLE         --SQL Server 2012

5. ALTER ANY SERVER ROLE       --SQL Server 2012

6. CONNECT ANY DATABASE        --SQL Server 2014

7. IMPERSONATE ANY LOGIN       --SQL Server 2014

8. SELECT ALL USER SECURABLES    --SQL Server 2014

可以通过系统函数sys.fn_builtin_permissions('SERVER') 查看实例的服务器级权限：

SELECT * FROM sys.fn_builtin_permissions('SERVER')
ORDER BY permission_name;

可以通过grant、revoke/deny赋予、收回或拒绝登陆账户服务器级权限，如：

use [master]
GO
--赋予登陆账户loginName查看服务状态权限
GRANT VIEW SERVER STATE TO [loginName]
--收回登陆账户loginName查看服务状态权限
REVOKE VIEW SERVER STATE FROM [loginName]
--拒绝登陆账户loginName 查看服务状态
DENY VIEW SERVER STATE TO [loginName]

赋予登录名或服务器角色权限时，如果增加选项 WITH GRANT OPTION，则登录名具有给其他登陆名赋予该权限的权限，如：

GRANT VIEW ANY DATABASE TO loginName WITH GRANT OPTION;

脚本意为登陆名loginName，授予查看任意数据库的权限，同时loginName也可以授予其他登陆名查看任意数据库的权限。

SQL Server 服务器级角色

 

角色可以看做有一定权限的集合，SQL Server 有8个固定服务器角色，包括：

• bulkadmin：OPENROWSET操作权限

• dbcreator：可以创建、更改、删除和还原任何数据库

  • ALTER ANY DATABASE

    • CREATE ANY DATABASE

• diskadmin：用于管理磁盘文件

• processadmin：是kill 连接，更改服务器状态的组合

  • ALTER ANY CONNECTION

  • ALTER SERVER STATE

    • VIEW SERVER STATE

• securityadmin：登录名创建、修改、删除，sp_addlinkedsrvlogin等权限

  • ALTER ANY LOGIN

• serveradmin：可以更改服务器范围的配置选项（sp_configure、reconfigure）和关闭服务器（shutdown）

  • ALTER ANY ENDPOINT

  • ALTER RESOURCES

  • ALTER SERVER STATE

    • VIEW SERVER STATE

  • SHUTDOWN

• setupadmin：sp_addlinkedser，创建链接服务器等权限

• sysadmin：拥有实例上的所有权限

• pubilc：所有登陆账户都是服务器角色public的成员，服务器角色public有许多授予系统对象的权限，这是管理内部操作所必需的。

  • VIEW ANY DATABASE

  • CONNECT ON ENDPOINT

  • ALTER ANY ENDPOINT

其中public较为特殊，所有登陆账户均为public角色成员，不能将登陆账户移除public角色成员。

 可以通过sp_addsrvrolemember 为服务器角色增加成员，如：

EXEC sp_addsrvrolemember loginName,sysadmin

脚本意为为固定角色sysadmin增加成员loginName。

为SQL Server登陆账户赋予SQL Server对象（主题）管理权限

在ssms中，右击登陆名，单击属性，从安全对象→搜索→对象类型中，我们可以看到，服务器级安全对象有五个：

• 端点

• 登陆名

• 服务器

• 可用性组

• 服务器角色

下面我们通过sys.fn_builtin_permissions来查看可以为登陆名赋予的各服务器安全对象的权限。可以给以登陆名对服务器的操作权限，上文已经列出，下面我们看服务器对其他四个安全对象的权限。

可用性组

SELECT * FROM sys.fn_builtin_permissions('AVAILABILITY GROUP');

我们看到，可以为登陆名授予对可用性组的操作权限有四个：

• VIEW DEFINITION

• ALTER

• TAKE OWNERSHIP

• CONTROL

端点

SELECT * FROM sys.fn_builtin_permissions('ENDPOINT');

我们看到，可以为登陆名授予对端点的操作权限有五个：

• CONNECT

• VIEW DEFINITION

• ALTER

• TAKE OWNERSHIP

• CONTROL

登录名

SELECT * FROM sys.fn_builtin_permissions('LOGIN');

• IMPERSONATE：模仿

• VIEW DEFINITION：查看定义

• ALTER：修改

• CONTROL：控制

服务器角色

SELECT * FROM sys.fn_builtin_permissions('SERVER ROLE');

• VIEW DEFINITION

• ALTER

• TAKE OWNERSHIP

• CONTROL

现在我们给出为登陆名赋予服务器级安全对象操作权限的样例：

--授予登陆账户loginName 拥有可用性组myAg,且loginName具有授予其他SQL Server 登陆名TAKE OWNERSHIP 权限授予
USE master; 
GRANT TAKE OWNERSHIP ON AVAILABILITY GROUP::myAg TO loginName  
    WITH GRANT OPTION; 
GO 
--赋予登陆名loginName查看端点endpoint_mirroring的权限
USE master; 
GRANT VIEW DEFINITION ON ENDPOINT::endpoint_mirroring TO loginName; 
GO 
--赋予登陆名loginName 查看登陆名testGrant定义的权限
use [master]
GO
GRANT VIEW DEFINITION ON LOGIN::[testGrant] TO [loginName]
GO
--赋予登陆名loginName2 模仿loginName1 的权限，即loginName1有的权限，loginName2现在也有了
GRANT IMPERSONATE ON LOGIN::[loginName1] TO [loginName2];
--赋予登陆名loginName查看服务角色serverRoleName定义权限
use [master]
GO
GRANT VIEW DEFINITION ON SERVER ROLE::[serverRoleName] TO [loginName]
GO

实验发现

grant alter on login::loginName1 to loginName2

为loginName2赋予修改loginName1的权限，但loginName2并不能对loginName1作任何alter操作。从alter login官方网上说明文档来看，alter login 需要alter any login权限，不知这是不是存在一个bug……

我们可以使用动态视图sys.server_permissions 结合 sys.server_principals，以及相关的对象视图查看登陆账户具有的权限，当然我们也可以直接使用《SQL Server 登陆账户权限克隆 ——sp_DBA_LoginClone V1.1》  中的过程查看登陆账户的权限。