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--所有者GOALTER 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]--先删除角色成员GODROP SERVER ROLE [NewTestServerRole]GO

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

4. 以单个权限授予对象

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

5. 相关目录视图

SELECT * FROM sys.server_principalsSELECT * 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 服务器角色和数据库角色相关操作