从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 服务器角色和数据库角色相关操作