SQL Server 账号管理主要包含登录名、用户、架构、角色等管理。通过对账号的管理可以有效的提高数据库系统的安全性，规范运维及使用。注意：登录名是实例下的安全对象，用户、架构及角色是数据库下的安全对象

安全认证的三个过程

用户访问数据库数据一般要经过三个安全认证过程:

1. 登录权限认证，访问实例 --- 登录名
2. 访问特定数据库的权限，访问数据库 --- 用户名
3. 在数据库特定对象上执行特定行为 --- 权限

登录名管理

使用 SSMS 创建 SQL Server 登录名：

1. 在对象资源管理器中，展开要在其中创建新登录名的服务器实例的文件夹。

2. 右键单击"安全性"文件夹，指向"新建"，然后选择"登录名…"。

用户名管理

使用 SSMS 创建用户：

1. 在对象资源管理器中 ，展开“数据库” 文件夹。
2. 展开要在其中创建新数据库用户的数据库。
3. 右键单击“安全”文件夹，指向“新建”，然后选择“用户...”。
4. 在 “数据库用户 - 新建 ”对话框中的 “常规 ”页上，从 “用户类型”列表中选择以下用户类型 之一：具有登录名的 SQL 用户

权限及权限控制

主体

“主体”是可以请求 SQL Server 资源的实体。

SQL Server 级的主体

• SQL Server 身份验证登录名
• Windows 用户的 Windows 身份验证登录名
• Windows 组的 Windows 身份验证登录名
• Microsoft Entra 用户的 Microsoft Entra 身份验证登录
• Microsoft Entra 组的 Microsoft Entra 身份验证登录
• 服务器角色

--列出服务器的主体
select * from sys.server_principals

数据库级的主体

• 数据库用户
• 数据库角色
• 应用程序角色

--列出数据库的主体
select * from sys.database_principals

安全对象

安全对象是 SQL Server 数据库引擎授权系统控制对其进行访问的资源

安全对象范围：服务器

• 可用性组
• 终结点
• 登录名
• 服务器角色
• 数据库

安全对象范围：数据库

• 应用程序角色
• 程序集
• 非对称密钥
• 证书
• 合约
• 全文目录
• 全文非索引字表
• 消息类型
• 远程服务绑定
• （数据库）角色
• 路由
• 架构
• 搜索属性列表
• 服务
• 对称密钥
• 用户

安全对象范围：架构

• 类型
• XML 架构集合
• 对象 - 对象类包含以下成员：
  • 聚合
  • 函数
  • 过程
  • 队列
  • 同义词
  • 表
  • 查看
  • 外部表

权限介绍

'主体'在‘安全对象上’可以执行什么操作或不可以执行什么操作。

--列出所有内置权限
SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

控制对安全对象的访问

可以通过 GRANT、REVOKE 和 DENY 语句将服务器级别权限应用于登录名或服务器角色，将数据库级别权限应用于用户或数据库角色

-- Simplified syntax for GRANT
GRANT { ALL [ PRIVILEGES ] }
      | permission [ ( column [ , ...n ] ) ] [ , ...n ]
      [ ON [ class :: ] securable ] TO principal [ , ...n ]
      [ WITH GRANT OPTION ] [ AS principal ]
      
--授予创建表的权限
USE testDB01;
GRANT CREATE TABLE TO testUser01;
GO
或
USE testDB01;
GRANT CREATE TABLE ON DATABASE::testDB01 TO  testUser01;
GO

--拒绝为主体授予权限。 防止该主体通过组或角色成员身份继承权限。
-- Simplified syntax for DENY  
DENY   { ALL [ PRIVILEGES ] } 
     | <permission>  [ ( column [ ,...n ] ) ] [ ,...n ]  
    [ ON [ <class> :: ] securable ] 
    TO principal [ ,...n ]   
    [ CASCADE] [ AS principal ]  
[;]

--拒绝为主体授予创建表的权限
USE testDB01;
DENY CREATE TABLE to  testUser01;
GO     
或
USE testDB01;
DENY CREATE TABLE ON DATABASE::testDB01  to  testUser01;
GO 

--撤消以前授予或拒绝的权限
REVOKE [ GRANT OPTION FOR ]  
      {   
        [ ALL [ PRIVILEGES ] ]  
        |  
                permission [ ( column [ ,...n ] ) ] [ ,...n ]  
      }  
      [ ON [ class :: ] securable ]   
      { TO | FROM } principal [ ,...n ]   
      [ CASCADE] [ AS principal ] 
      
--撤销创建表的权限
USE testDB01;
REVOKE CREATE TABLE from  testUser01;
GO     
或
USE testDB01;
REVOKE CREATE TABLE ON DATABASE::testDB01  from  testUser01;
GO 

取消DENY示例

权限：GRANT、DENY、REVOKE

--以下 GRANT 语句授予 testUser02 在 testDB01 库中创建表的权限。
USE testDB01;
GRANT CREATE TABLE TO testUser02;
GO

--以下 DENY 语句阻止 testUser02 在 testDB01 库中创建表的权限。 
USE testDB01;
DENY CREATE TABLE to  testUser02;
GO    

--以下 REVOKE 语句会删除 DENY 权限 。
USE testDB01;
REVOKE CREATE TABLE from  testUser02;
GO 

--列出数据库主体的所有权限( 不含固定数据库角色的权限 )
SELECT pr.principal_id, , pr.type_desc,   
    pr.authentication_type_desc, pe.state_desc, pe.permission_name  
FROM sys.database_principals AS pr  
JOIN sys.database_permissions AS pe  
    ON pe.grantee_principal_id = pr.principal_id;