SQL Server角色以及权限概述及示例演示

SQL Server具有两种级别的角色，数据库级别的角色、服务器级别角色。

一、固定服务器角色在其作用域内属于服务器范围。固定服务器角色的每个成员都可以向其所属角色添加其他登录名。 

固定服务器角色有：

bulkadmin 已授予：ADMINISTER BULK OPERATIONS;
bulkadmin 固定服务器角色的成员可以运行 BULK INSERT 语句。

dbcreator 已授予：CREATE DATABASE;
dbcreator 固定服务器角色的成员可以创建数据库，并可以更改和还原其自己的数据库。

diskadmin 已授予：ALTER RESOURCES;
diskadmin 固定服务器角色用于管理磁盘文件。 

processadmin 已授予：ALTER ANY CONNECTION、ALTER SERVER STATE;
processadmin 固定服务器角色的成员可以终止 SQL Server 实例中运行的进程。 

securityadmin 已授予：ALTER ANY LOGIN;
securityadmin 固定服务器角色的成员将管理登录名及其属性。它们可以 GRANT、DENY 和 REVOKE 服务器级权限。也可以 GRANT、DENY 和 REVOKE; 数据库级权限。另外，它们可以重置 SQL Server 登录名的密码。 

serveradmin 已授予：ALTER ANY ENDPOINT、ALTER RESOURCES、ALTER SERVER STATE、ALTER SETTINGS、SHUTDOWN、VIEW SERVER STATE;
serveradmin 固定服务器角色的成员可以更改服务器范围的配置选项和关闭服务器。 

setupadmin 已授予：ALTER ANY LINKED SERVER;
setupadmin 固定服务器角色的成员可以添加和删除链接服务器，并且也可以执行某些系统存储过程。

sysadmin 已使用 GRANT 选项授予：CONTROL SERVER;
sysadmin 固定服务器角色的成员可以在服务器中执行任何活动。默认情况下，Windows BUILTIN\Administrators 组（本地管理员组）的所有成员都是 sysadmin 固定服务器角色的成员。  

授予角色示例：
--给登陆用户td授予dbcreator服务器角色
EXEC master..sp_addsrvrolemember @loginame = N‘td‘, @rolename = N‘dbcreator‘
GO
--撤销登陆用户td所拥有的dbcreator服务器角色
EXEC master..sp_dropsrvrolemember @loginame = N‘td‘, @rolename = N‘dbcreator‘
GO

查询用户具有服务器级别角色的情况:
select loginname,denylogin,hasaccess,isntname,isntgroup,isntuser,sysadmin,securityadmin,serveradmin,setupadmin,processadmin,diskadmin,dbcreator,bulkadmin
 from master..syslogins

二、数据库级别的角色
参考：http://msdn.microsoft.com/zh-cn/library/ms189121(v=sql.90).aspx

数据库级别的角色：固定数据库角色是在数据库级别定义的，并且存在于每个数据库中。db_owner 和 db_securityadmin 数据库角色的成员可以管理固定数据库角色成员身份；但是，只有 db_owner 数据库的成员可以向 db_owner 固定数据库角色中添加成员。 

db_accessadmin 已授予：ALTER ANY USER、CREATE SCHEMA，已使用 GRANT 选项授予：CONNECT;
db_accessadmin 固定数据库角色的成员可以为 Windows 登录帐户、Windows 组和 SQL Server 登录帐户添加或删除访问权限。 

db_backupoperator 已授予：BACKUP DATABASE、BACKUP LOG、CHECKPOINT;
db_backupoperator 固定数据库角色的成员可以备份该数据库。 

db_datareader 已授予：SELECT;
db_datareader 固定数据库角色的成员可以对数据库中的任何表或视图运行 SELECT 语句。

db_datawriter 已授予：DELETE、INSERT、UPDATE;
db_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。 

db_ddladmin 已授予：ALTER ANY ASSEMBLY、ALTER ANY ASYMMETRIC KEY、ALTER ANY CERTIFICATE、ALTER ANY CONTRACT、ALTER ANY DATABASE DDL TRIGGER、ALTER ANY DATABASE EVENT、NOTIFICATION、ALTER ANY DATASPACE、ALTER ANY FULLTEXT CATALOG、ALTER ANY MESSAGE TYPE、ALTER ANY REMOTE SERVICE BINDING、ALTER ANY ROUTE、ALTER ANY SCHEMA、ALTER ANY SERVICE、ALTER ANY SYMMETRIC KEY、CHECKPOINT、CREATE AGGREGATE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE QUEUE、CREATE RULE、CREATE SYNONYM、CREATE TABLE、CREATE TYPE、CREATE VIEW、CREATE XML SCHEMA COLLECTION、REFERENCES;
db_ddladmin 固定数据库角色的成员可以在数据库中运行任何数据定义语言 (DDL) 命令。 

db_denydatareader 已拒绝：SELECT;
db_denydatareader 固定服务器角色的成员不能读取数据库内用户表中的任何数据。 

db_denydatawriter 已拒绝：DELETE、INSERT、UPDATE;
db_denydatawriter 固定服务器角色的成员不能添加、修改或删除数据库内用户表中的任何数据。 

db_owner 已使用 GRANT 选项授予：CONTROL;
db_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动。 

db_securityadmin 已授予：ALTER ANY APPLICATION ROLE、ALTER ANY ROLE、CREATE SCHEMA、VIEW DEFINITION;
db_securityadmin 固定数据库角色的成员可以修改角色成员身份和管理权限。 

 授予角色示例:

--撤销用户test的数据库角色(db_owner)：
USE [dbName]
GO
EXEC sp_droprolemember N‘db_owner‘, N‘test‘
GO
--给用户test授予数据库角色(db_owner)
USE [dbName]
GO
EXEC sp_addrolemember N‘db_owner‘, N‘test‘
GO

--查询的sql：

Declare @Name varchar(100)
declare @sql_text varchar(2000)
Declare Cur Cursor For select name from master..sysdatabases
CREATE Table #tb_dbrole ( dbname varchar(100),userName varchar(100),roleName varchar(100));
Open Cur Fetch next From Cur Into @Name
While @@fetch_status=0
Begin
set @sql_text = ‘insert into #tb_dbrole select ‘‘‘+@Name+‘‘‘,a.name as userName,b.name as roleName
from ‘ + @Name + ‘..sysusers a inner join ‘ + @Name + ‘..sysmembers m on m.memberuid = a.uid
inner join ‘ + @Name + ‘..sysusers b on b.gid = m.groupuid
where a.issqluser =1‘
Exec (@sql_text);
Fetch Next From Cur Into @Name End ;
Close Cur
Deallocate Cur
select * from #tb_dbrole order by dbname;
drop table #tb_dbrole;

三、查询所有的系统权限

查询的sql：

Declare @Name varchar(30)
declare @sql_text varchar(2000)
Declare Cur Cursor For select name from master..sysdatabases
CREATE Table #tb_syspriv ( dbname varchar(30),username varchar(30),privilegeName varchar(30),PROTECTTYPE varchar(30) );
Open Cur Fetch next From Cur Into @Name
While @@fetch_status=0
Begin
set @sql_text = ‘insert into #tb_syspriv select ‘‘‘+@Name+‘‘‘,user_name(uid) username,
CASE ACTION WHEN  178 THEN ‘‘CREATE FUNCTION‘‘
WHEN 198 THEN ‘‘CREATE TABLE‘‘
WHEN 203 THEN ‘‘CREATE DATABASE‘‘
WHEN 207 THEN ‘‘CREATE VIEW‘‘
WHEN 222 THEN ‘‘CREATE PROCEDURE‘‘
WHEN 228 THEN ‘‘BACKUP DATABASE‘‘
WHEN 233 THEN ‘‘CREATE DEFAULT‘‘
WHEN 235 THEN ‘‘BACKUP LOG‘‘
WHEN 224 THEN ‘‘EXECUTE‘‘
WHEN 236 THEN ‘‘CREATE RULE‘‘ end as privilegeName,
CASE PROTECTTYPE
WHEN 204 THEN ‘‘GRANT_W_GRANT ‘‘
WHEN 205 THEN ‘‘GRANT‘‘
WHEN 206 THEN ‘‘DENY‘‘
ELSE ‘‘OTHER‘‘
END AS PROTECTTYPE
FROM ‘ + @Name + ‘..sysprotects WHERE id=0 AND PROTECTTYPE = 205 ‘
print (@sql_text)
Exec (@sql_text);
Fetch Next From Cur Into @Name End ;
Close Cur
Deallocate Cur
select * from #tb_syspriv order by dbname;
drop table #tb_syspriv;

四、查询所有的用户权限

查询的sql：

Declare @Name varchar(100)
declare @sql_text varchar(2000)
Declare Cur Cursor For select name from master..sysdatabases
CREATE Table #tb_objpriv ( dbname varchar(100),userName varchar(100),objname varchar(100),objTYPE varchar(30),privilegeName varchar(100),PROTECTTYPE varchar(100) );
Open Cur Fetch next From Cur Into @Name
While @@fetch_status=0
Begin
set @sql_text = ‘insert into #tb_objpriv select ‘‘‘+@Name+‘‘‘,b.name as userName,c.name as objname,
CASE b.type
WHEN ‘‘U‘‘ THEN ‘‘Table‘‘
WHEN ‘‘P‘‘ THEN ‘‘SP‘‘
ELSE b.type
END AS objTYPE,
CASE ACTION
WHEN 26 THEN ‘‘REFERENCES‘‘
WHEN 193 THEN ‘‘SELECT‘‘
WHEN 195 THEN ‘‘INSERT‘‘
WHEN 197 THEN ‘‘UPDATE‘‘
WHEN 196 THEN ‘‘DELETE‘‘
WHEN 224 THEN ‘‘EXECUTE‘‘
else ‘‘OTHER‘‘
end as privilegeName,
CASE a.PROTECTTYPE
WHEN 204 THEN ‘‘GRANT_W_GRANT‘‘
WHEN 205 THEN ‘‘GRANT‘‘
WHEN 206 THEN ‘‘DENY‘‘
ELSE ‘‘OTHER‘‘
END AS PROTECTTYPE
from ‘ + @Name + ‘..sysprotects a INNER join ‘ + @Name + ‘..sysobjects b on a.id = b.id
inner join ‘ + @Name + ‘..sysusers c on a.uid = c.uid AND a.PROTECTTYPE = 205‘
Exec (@sql_text);
Fetch Next From Cur Into @Name End ;
Close Cur
Deallocate Cur
select * from #tb_objpriv order by dbname;
drop table #tb_objpriv;

五、几种不同的写法

--读取对象权限
select b.name as tName,c.name as objname,
CASE b.type
WHEN ‘U‘ THEN ‘Table‘
WHEN ‘P‘ THEN ‘SP‘
ELSE ‘OTHER‘
END AS TYPE,
CASE WHEN  a.ACTION = 26 AND a.PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘REFERENCES‘,
CASE WHEN  a.ACTION = 193 AND a.PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘SELECT‘,
CASE WHEN  a.ACTION = 195 AND a.PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘INSERT‘,
CASE WHEN  a.ACTION = 197 AND a.PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘UPDATE‘,
CASE WHEN  a.ACTION = 196 AND a.PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘DELETE‘,
CASE WHEN  a.ACTION = 224 AND a.PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘EXECUTE‘,
CASE a.PROTECTTYPE
WHEN 204 THEN ‘GRANT_W_GRANT ‘
WHEN 205 THEN ‘GRANT‘
WHEN 206 THEN ‘DENY‘
ELSE ‘OTHER‘  --当有other出现的时候，需要将其他的PROTECTTYPE添加进去。
END AS PROTECTTYPE
from sysprotects a INNER join sysobjects b on a.id = b.id
inner join sysusers c on a.uid = c.uid
--读取对象权限2
select b.name as tName,c.name as objname,
CASE b.type
WHEN ‘U‘ THEN ‘Table‘
WHEN ‘P‘ THEN ‘SP‘
ELSE b.type
END AS TYPE,
CASE ACTION
WHEN 26 THEN ‘REFERENCES‘
WHEN 193 THEN ‘SELECT‘
WHEN 195 THEN ‘INSERT‘
WHEN 197 THEN ‘UPDATE‘
WHEN 196 THEN ‘DELETE‘
WHEN 224 THEN ‘EXECUTE‘
else ‘OTHER‘
end as privilegeName,
CASE a.PROTECTTYPE
WHEN 204 THEN ‘GRANT_W_GRANT ‘
WHEN 205 THEN ‘GRANT‘
WHEN 206 THEN ‘DENY‘
ELSE ‘OTHER‘  --当有other出现的时候，需要将其他的PROTECTTYPE添加进去。
END AS PROTECTTYPE
from sysprotects a INNER join sysobjects b on a.id = b.id
inner join sysusers c on a.uid = c.uid AND a.PROTECTTYPE = 205
--查询系统权限
SELECT user_name(uid) username,
CASE WHEN  ACTION = 178 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘CREATE FUNCTION‘,
CASE WHEN  ACTION = 198 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘CREATE TABLE‘,
CASE WHEN  ACTION = 203 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘CREATE DATABASE‘,
CASE WHEN  ACTION = 207 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘CREATE VIEW‘,
CASE WHEN  ACTION = 222 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘CREATE PROCEDURE‘,
CASE WHEN  ACTION = 228 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘BACKUP DATABASE‘,
CASE WHEN  ACTION = 233 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘CREATE DEFAULT‘,
CASE WHEN  ACTION = 235 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘BACKUP LOG‘,
CASE WHEN  ACTION = 236 AND PROTECTTYPE = 205 THEN ‘√‘ ELSE ‘‘ END AS ‘CREATE RULE‘,
CASE PROTECTTYPE
WHEN 204 THEN ‘GRANT_W_GRANT ‘
WHEN 205 THEN ‘GRANT‘
WHEN 206 THEN ‘DENY‘
ELSE ‘OTHER‘  --当有other出现的时候，需要将其他的PROTECTTYPE添加进去。
END AS PROTECTTYPE
FROM sysprotects WHERE columns is null
--查询系统权限
SELECT user_name(uid) username,
CASE ACTION WHEN  178 THEN ‘CREATE FUNCTION‘
WHEN 198 THEN ‘CREATE TABLE‘
WHEN 203 THEN ‘CREATE DATABASE‘
WHEN 207 THEN ‘CREATE VIEW‘
WHEN 222 THEN ‘CREATE PROCEDURE‘
WHEN 228 THEN ‘BACKUP DATABASE‘
WHEN 233 THEN ‘CREATE DEFAULT‘
WHEN 235 THEN ‘BACKUP LOG‘
WHEN 236 THEN ‘CREATE RULE‘ end as privilegeName,
CASE PROTECTTYPE
WHEN 204 THEN ‘GRANT_W_GRANT ‘
WHEN 205 THEN ‘GRANT‘
WHEN 206 THEN ‘DENY‘
ELSE ‘OTHER‘  --当有other出现的时候，需要将其他的PROTECTTYPE添加进去。
END AS PROTECTTYPE
FROM sysprotects WHERE columns is null AND PROTECTTYPE = 205

SQL Server角色以及权限概述及示例演示,古老的榕树,5-wow.com