SqlServer 2012 服务器角色

从SqlServer 2012 开始，MSSQL 可以创建服务器角色！服务器级别的权限可以任意控制了！

数据库默认有9种固定服务器角色：Bulkadmin、Dbcreator、Diskadmin、Processadmin、Securityadmin、Serveradmin、Setupadmin、setupadmin、Sysadmin、Public

服务器角色在安全对象中，如图：

右键 “服务器角色”  可以图形界面创建服务器角色，以下以命令方式创建：

创建服务器角色所需权限： sysadmin  或 CONTROL SERVER  或 ALTER ANY SERVER ROLE

1. 创建服务器角色

--	【创建服务器角色】
CREATE SERVER ROLE TestServerRole	--服务器角色
AUTHORIZATION KK	--所有者
GO

ALTER AUTHORIZATION ON SERVER ROLE::[TestServerRole] TO [PC\Administrator]--更改所有者为[PC\Administrator]
GO

2. 更改服务器角色

--	【更改服务器角色】
--	注: 以下MEMBER 可以是登录名或用户定义的服务器角色。MEMBER 不能是固定服务器角色、数据库角色或 sa。

--即登录账号[kk]拥有服务器角色[TestServerRole]权限
ALTER SERVER ROLE [TestServerRole] ADD MEMBER [kk]	
GO

--更名
ALTER SERVER ROLE [TestServerRole] WITH NAME = [NewTestServerRole]	
GO

--即服务器角色[TestServerRole]拥有服务器角色[dbcreator]权限
ALTER SERVER ROLE [dbcreator] ADD MEMBER [NewTestServerRole]	
GO

可以看到服务器角色名字变更了，同时角色成员用记录有登录账号 “KK”

数据库角色 NewTestServerRole 的服务器角色成关系中 勾选上了 dbcreator ，即服务器角色 NewTestServerRole 也有了角色 dbcreator  的权限！

打开登录右键登录名 “KK” ，可以看到其拥有的服务器角色。

3. 删除服务器角色

--	【删除服务器角色】
ALTER SERVER ROLE [NewTestServerRole] DROP MEMBER [kk]	--先删除角色成员
GO
DROP SERVER ROLE [NewTestServerRole]
GO

注: 大部分系统存储过程已不推荐使用,如 sp_addsrvrolemember 和 sp_dropsrvrolemember

4. 以单个权限授予对象

--	【以单个权限授予对象】
GRANT CONNECT SQL TO [TestServerRole]
GO
ALTER SERVER ROLE [TestServerRole] ADD MEMBER [kk]
GO
--	权限等价于下面对登录账号 [kk] 的单独授权
GRANT CONNECT SQL TO [kk] AS [sa]
GO

5. 相关目录视图

SELECT * FROM sys.server_principals
SELECT * FROM sys.server_role_members

--	服务器角色和成员关系
SELECT rsp.principal_id as [role_principal_id],rsp.name AS [Server_Role],sp.principal_id
,sp.name,sp.[sid],sp.[type] AS [Principal_Type],sp.create_date,sp.modify_date,sp.is_disabled  
FROM sys.server_principals sp   
INNER JOIN sys.server_role_members srm ON sp.principal_id=srm.member_principal_id   
INNER JOIN sys.server_principals rsp ON srm.role_principal_id=rsp.principal_id

--	服务器登录账号拥有的详细权限
SELECT DISTINCT sp1.name AS grantor_name,sp1.type_desc AS grantor_type_desc ,sp2.name AS grantee_name
,sp2.type_desc AS grantee_type_desc ,spe.class_desc,spe.state_desc,spe.permission_name  
FROM sys.server_principals sp1  
INNER JOIN sys.server_permissions spe ON sp1.principal_id=spe.grantor_principal_id  
INNER JOIN sys.server_principals sp2 ON sp2.principal_id=spe.grantee_principal_id 

更多服务器角色和数据库角色操作，参考：SqlServer 服务器角色和数据库角色相关操作