SQL Server 2008共有26个服务器级权限,分别为
-
ADMINISTER BULK OPERATIONS
-
ALTER ANY CONNECTION
-
ALTER ANY CREDENTIAL
-
CREATE ANY DATABASE
-
ALTER ANY DATABASE
-
VIEW ANY DATABASE
-
CREATE ENDPOINT
-
ALTER ANY ENDPOINT
-
CREATE DDL EVENT NOTIFICATION
-
CREATE TRACE EVENT NOTIFICATION
-
ALTER ANY EVENT NOTIFICATION
-
ALTER ANY LINKED SERVER
-
ALTER ANY LOGIN
-
ALTER ANY SERVER AUDIT
-
ALTER RES
-
ALTER TRACE
-
AUTHENTICATE SERVER
-
CONNECT SQL
-
CONTROL SERVER
-
EXTERNAL ACCESS ASSEMBLY
-
SHUTDOWN
-
UNSAFE ASSEMBLY
-
VIEW ANY DEFINITION
-
VIEW SERVER STATE
-
OURCES
-
ALTER SERVER STATE
-
ALTER SETTINGS
SQL Server 2016 中比 2008 多出了如下8个服务级权限
-
CREATE AVAILABILITY GROUP --SQL Server 2012
-
ALTER ANY AVAILABILITY GROUP --SQL Server 2012
-
ALTER ANY EVENT SESSION
-
CREATE SERVER ROLE --SQL Server 2012
-
ALTER ANY SERVER ROLE --SQL Server 2012
-
CONNECT ANY DATABASE --SQL Server 2014
-
IMPERSONATE ANY LOGIN --SQL Server 2014
-
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》 中的过程查看登陆账户的权限。