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

安全认证的三个过程

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

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

SQL Server 账号管理1_SQL Server 级的主体

登录名管理

使用 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
  • 1.
  • 2.
数据库级的主体
  • 数据库用户
  • 数据库角色
  • 应用程序角色
--列出数据库的主体
select * from sys.database_principals
  • 1.
  • 2.

安全对象

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

安全对象范围：服务器
  • 可用性组
  • 终结点
  • 登录名
  • 服务器角色
  • 数据库
安全对象范围：数据库
  • 应用程序角色
  • 程序集
  • 非对称密钥
  • 证书
  • 合约
  • 全文目录
  • 全文非索引字表
  • 消息类型
  • 远程服务绑定
  • （数据库）角色
  • 路由
  • 架构
  • 搜索属性列表
  • 服务
  • 对称密钥
  • 用户
安全对象范围：架构
  • 类型
  • XML 架构集合
  • 对象 - 对象类包含以下成员：
    • 聚合
    • 函数
    • 过程
    • 队列
    • 同义词
    • 表
    • 查看
    • 外部表

权限介绍

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

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

控制对安全对象的访问

可以通过 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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
--拒绝为主体授予权限。 防止该主体通过组或角色成员身份继承权限。
-- 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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
--撤消以前授予或拒绝的权限
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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.

取消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
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
--列出数据库主体的所有权限( 不含固定数据库角色的权限 )
SELECT pr.principal_id, pr.name, 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;
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.