角色管理

角色是一个权限的集合，只要将用户加入到角色成员（给用户分配一个角色），就可以给这个用户分配这个角色所具有的全部权限。角色的出现极大的简化了权限的管理。

服务器角色

服务器角色针对的的安全主体，一般是登录名

服务器级的固定角色说明
sysadminsysadmin 固定服务器角色的成员可以在服务器上执行任何活动。
serveradminserveradmin 固定服务器角色的成员可以更改服务器范围的配置选项和关闭服务器。
securityadminsecurityadmin 固定服务器角色的成员可以管理登录名及其属性。 他们可以 GRANT、DENY 和 REVOKE 服务器级权限。 securityadmin 还可以 GRANT、DENY和 REVOKE 数据库级权限（如果他们具有数据库的访问权限）。 此外，securityadmin 还可以重置 SQL Server 登录名的密码。 重要提示：授予数据库引擎的访问权限和配置用户权限的能力使得安全管理员可以分配大多数服务器权限。 securityadmin 角色应视为与 sysadmin 角色等效。 或者，从 SQL Server 2022 (16.x) 开始，请考虑使用新的固定服务器角色 ##MS_LoginManager##。
processadminprocessadmin 固定服务器角色的成员可以终止在 SQL Server 实例中运行的进程。
setupadminsetupadmin 固定服务器角色的成员可以使用 Transact-SQL 语句添加和删除链接服务器。 （使用 Management Studio 时需要 sysadmin 成员资格。）
bulkadminbulkadmin 固定服务器角色的成员可以运行 BULK INSERT 语句。 Linux 上的 SQL Server 不支持 bulkadmin 角色或 ADMINISTER BULK OPERATIONS 权限。 只有 sysadmin 才能对 Linux 上的 SQL Server 执行批量插入。
diskadmindiskadmin 固定服务器角色用于管理磁盘文件。
dbcreatordbcreator 固定服务器角色的成员可以创建、更改、删除和还原任何数据库。
public每个 SQL Server 登录名都属于 public 服务器角色。 如果未向某个服务器主体授予或拒绝对某个安全对象的特定权限，用户将继承向 public 角色授予的对该对象的权限。
----列出服务器级别权限
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
  • 1.
  • 2.

数据库角色

数据库角色针对的安全主体，一般是用户

固定数据库角色名说明
db_ownerdb_owner 固定数据库角色的成员可以执行数据库的所有配置和维护活动，还可以 drop SQL Server 中的数据库。 （在 SQL 数据库 和 Synapse Analytics 中，某些维护活动需要服务器级别权限，并且不能由 db_owners 执行。）
db_securityadmindb_securityadmin 固定数据库角色的成员可以仅修改自定义角色的角色成员资格和管理权限。 此角色的成员可能会提升其权限，应监视其操作。
db_accessadmindb_accessadmin 固定数据库角色的成员可以为 Windows 登录名、Windows 组和 SQL Server 登录名添加或删除数据库访问权限。
db_backupoperatordb_backupoperator 固定数据库角色的成员可以备份数据库。
db_ddladmindb_ddladmin 固定数据库角色的成员可以在数据库中运行任何数据定义语言 (DDL) 命令。 此角色的成员可以通过操作可能以高特权执行的代码来提升其特权，其操作应被监视。
db_datawriterdb_datawriter 固定数据库角色的成员可以在所有用户表中添加、删除或更改数据。 在大多数用例下，此角色将与 db_datareader 成员身份一起使用，以允许读取要修改的数据。
db_datareaderdb_datareader 固定数据库角色的成员可以从所有用户表和视图中读取所有数据。 用户对象可能存在于除 sys 和 INFORMATION_SCHEMA 以外的任何架构中。
db_denydatawriterdb_denydatawriter 固定数据库角色的成员不能添加、修改或删除数据库内用户表中的任何数据。
db_denydatareaderdb_denydatareader 固定数据库角色的成员不能读取数据库内用户表和视图中的任何数据。
public每个数据库用户都属于 public 数据库角色。 如果未向某个用户授予或拒绝对安全对象的特定权限时，该用户将继承授予该对象的 public 角色的权限。 无法将数据库用户从 public 角色删除。

用户定义数据库角色

功能类型描述
 CREATE ROLE (Transact-SQL)命令在当前数据库中创建新的数据库角色。
 ALTER ROLE (Transact-SQL)命令更改数据库角色的名称或成员身份。
 DROP ROLE (Transact-SQL)命令从数据库中删除角色。
 GRANT权限向角色添加权限。
 DENY权限拒绝向角色授予权限。
 REVOKE权限撤消以前授予或拒绝的权限。

示例：

CREATE ROLE role_name  AUTHORIZATION owner_name; 

GRANT SELECT ON SCHEMA::dbo   TO role01;
GRANT INSERT  ON SCHEMA::dbo  TO role01;
GRANT DELETE  ON SCHEMA::dbo  TO role01;

ALTER ROLE role01 ADD MEMBER testUser01;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
--将返回任何数据库角色的所有成员
SELECT    roles.principal_id                            AS RolePrincipalID
    ,    roles.name                                    AS RolePrincipalName
    ,    database_role_members.member_principal_id    AS MemberPrincipalID
    ,    members.name                                AS MemberPrincipalName
FROM sys.database_role_members AS database_role_members  
JOIN sys.database_principals AS roles  
    ON database_role_members.role_principal_id = roles.principal_id  
JOIN sys.database_principals AS members  
    ON database_role_members.member_principal_id = members.principal_id;  
GO
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

架构管理

架构介绍

架构是一个适用于数据库对象的命名容器，它使你能够将对象分组到单独的命名空间中。通过用户架构分离，可实现管理数据库对象权限的更大灵活性。任何数据库主体都可以拥有架构，并且一个主体可拥有多个架构。 如果设置了对架构的访问权限，则当新对象添加到架构时，新对象会自动应用这些权限。 可以为用户分配一个默认的架构，且多个数据库用户可以共享同一架构。

使用SSMS 创建架构

架构是指包含表、视图、存储过程等数据库对象的容器。使用 SQL Server Management Studio 创建架构：

  1. 在对象资源管理器中，展开 “数据库” 文件夹。

  2. 展开要在其中创建新数据库架构的数据库。

  3. 右键单击“安全性”文件夹，指向“新建”，并选择“架构”。

  4. 在“架构 - 新建”对话框中的“常规”页上，在“架构名称”框中输入新架构的名称。

  5. 在 “架构所有者” 框中，输入要拥有该架构的数据库用户或角色的名称。 或者，选择“搜索” 以打开“搜索角色和用户”对话框。

  6. 选择“确定”。

使用 Transact-SQL 创建架构

USE [testDB01]
GO
CREATE SCHEMA [testsch] AUTHORIZATION [dbo]
GO
  • 1.
  • 2.
  • 3.
  • 4.