如何拒绝写入所有 SQL Server 数据库（包括新的和恢复的）

如果您可以在执行之间有有限的时间（最少 10 秒）来捕获新的/恢复的数据库，您可以创建一个计划的 SQL Server 代理作业并将类似这样的内容放入 T-SQL 作业步骤：-

SET NOCOUNT ON

DECLARE @command nvarchar(max) ;

SET @command = 
N'SET NOCOUNT ON

DECLARE @UserName sysname ;

SELECT @UserName = USRS.[name] 
FROM sys.database_principals AS USRS
INNER JOIN sys.server_principals AS LGNS ON LGNS.[sid] = USRS.[sid]
WHERE LGNS.[name] = ''LoginName'' ;

IF IS_ROLEMEMBER(''db_denydatawriter'', @UserName) = 0
   BEGIN
      EXEC sp_addrolemember ''db_denydatawriter'', @UserName ;
   END ;' ;

EXEC sp_ineachdb 
   @command      = @command, 
   @state_desc   = N'ONLINE', 
   @is_read_only = 0 ;

你不能在 IUD 上使用登录触发器和拒绝吗？每次用户登录时，我想它都需要遍历数据库并将 DENY 添加到任何新添加的数据库中。

有人可以使这更简单，但这是我的方法：

1. 为“只读管理员”创建服务器角色并分配以下权限以允许特定功能的服务器范围权限，例如查看所有对象的定义和查看服务器状态的能力。这也为您要分配给各个数据库角色的用户创建了一个“桶”：

   USE [master]
   GO
   CREATE SERVER ROLE [ReadOnlyAdmins]
   GO
   GRANT CONNECT SQL TO [ReadOnlyAdmins]
   GO
   GRANT VIEW ANY DATABASE TO [ReadOnlyAdmins]
   GO
   GRANT VIEW ANY DEFINITION TO [ReadOnlyAdmins]
   GO
   GRANT VIEW SERVER STATE TO [ReadOnlyAdmins]
   GO
   

2. 在 [master] 上创建存储过程以创建/更新特定于数据库的用户组，该用户组授予对指定数据库的读取但拒绝写入。我正在使用存储过程，因为您很可能需要在指定数据库的上下文中运行，因此动态 sql 是我想到的第一个解决方案。请注意，此解决方案强制您使用特定的组名，您可以将其更改为您喜欢的任何名称。

编辑：如另一个解决方案中所述，存储过程需要检查正在添加/更新的数据库是否处于联机模式。如果不是，它应该每 5 秒左右循环并检查一次吗？

    CREATE PROCEDURE dbo.CreateDBReadOnlyAdmins
    (
         @dbName NVARCHAR(128)
    )
    AS
    BEGIN
    SET NOCOUNT ON;
    DECLARE @dbState NVARCHAR(500)
    select @dbState = state_desc from sys.databases where name = @dbName
    WHILE(@dbState <> 'ONLINE')
       BEGIN
          WAITFOR DELAY '00:00:05'
          select @dbState = state_desc from sys.databases where name = @dbName 
       END 
    DECLARE @sql NVARCHAR(MAX), @max_id int, @id int, @retval int, @ParmDefinition                 nvarchar(500);
    --Check to see if the Database Role has been created or not.  Add it if not.
    SET @sql = 'SET NOCOUNT ON;SELECT 1 FROM ' + @dbName + '.sys.database_principals                 WHERE type_desc = ''DATABASE_ROLE'' AND name = ''ReadOnlyAdmins'';'
    SET @ParmDefinition = N'@retvalOUT int OUTPUT';
    EXEC sp_executesql @SQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;
    SELECT @id = ISNULL(@retval,0);
    IF (@id = 0)
        BEGIN 
            SET @sql = 'USE [' + @dbName + '];' +
            'CREATE ROLE [ReadOnlyAdmins];' +
            'GRANT SELECT ON ALL TO [ReadOnlyAdmins];' +
            'DENY INSERT, UPDATE, DELETE ON ALL TO [ReadOnlyAdmins];';
            EXEC @sql
            --SELECT (@sql)
        END
    
    
    IF(OBJECT_ID('tempdb..#role_members') IS NOT NULL) BEGIN DROP TABLE #role_members END;
    --Add all members of the ReadOnlyAdmins Server Role to the db-specific role.
    SELECT ROW_NUMBER() OVER (ORDER BY member.name) as rowid
        ,'ALTER ROLE [ReadOnlyAdmins] ADD MEMBER [' + member.name + '];' AS MemberName
    into #role_members
    FROM sys.server_role_members  
    JOIN sys.server_principals AS role  
        ON sys.server_role_members.role_principal_id = role.principal_id  
    JOIN sys.server_principals AS member  
        ON sys.server_role_members.member_principal_id = member.principal_id
    WHERE role.name = 'ReadOnlyAdmins';
    
    SET @sql = 'USE [' + @dbName + '];'
    
    select @max_id = MAX(rowid) from #role_members
    SET @id = 1
    
    WHILE @id <= @max_id
        BEGIN
            SELECT @sql = @sql + MemberName from #role_members where rowid = @id
            EXEC @sql
            --SELECT @sql
            SET @id = @id + 1
        END
    END
    GO

3. 接下来是添加一个 ddl 触发器来覆盖所有 CREATE_DATABASE 和 ALTER_DATABASE 事件。此触发器需要进行微调以处理高可用性场景以及对兼容性、恢复模型等的随机更改。触发器还将解析出数据库名称。我从 [Bob Pusateri] https://www.bobpusateri.com/archive/2018/09/a-tale-of-a-trigger/ 窃取 HA 处理。此外，我不确定如何防止触发器触发非侵入性更新（例如兼容性、恢复模式、用户模式等）。我想一个更聪明的人需要处理这个问题。:)

   CREATE TRIGGER AddReadOnlyAdminsToDBs
   ON ALL SERVER
   FOR CREATE_DATABASE, ALTER_DATABASE
   AS
   BEGIN
       DECLARE @xml XML = EVENTDATA();
       DECLARE @var_xml NVARCHAR(128);
       DECLARE @error_msg VARCHAR(1024);
   
       SET @var_xml.value('(EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(128)') 
   
       --uncomment the below line if you are running HADR
       --If sys.fn_hadr_is_primary_replica(@var_xml) = 1   
       BEGIN  
           EXEC master.dbo.CreateDBReadOnlyAdmins @var_xml 
       END  
   END
   GO
   

4. 现在，无论何时在实例上添加新数据库或更新数据库，上述内容都涵盖了。当您只是在上面的 SERVER ROLE 中添加新登录时，您会怎么做？我的想法是创建另一个存储过程（或仅更改上述存储过程以处理所有本地数据库，然后将 SERVER ROLE 中的所有用户添加到所有非系统数据库）。然后将其与另一个仅查看 ADD_SERVER_ROLE_MEMBER 事件然后仅查看特定 SERVER ROLE“ReadOnlyAdmins”的 DDL 触发器结合使用。

我发现的其他漏洞：

• 如何处理 SQL 代理。我的意思是您可以为 MSDB 自定义创建一个特定于数据库的角色，以涵盖检查作业状态、查看作业日志等内容。
• 如何处理复制。我不确定如何处理这种特殊情况。我需要更多地研究它。
• 一些基于策略的管理能否更好地满足这一点？再一次，我需要做更多的研究。

谢谢。

我发现这也许可以帮助恢复部分。

在任何 RESTORE DATABASE 事件后自动执行存储过程

DECLARE @fn VARCHAR(MAX); 
SELECT @fn = SUBSTRING([path], 0, LEN([path])-CHARINDEX(CHAR(92), REVERSE([path]))+1) + CHAR(92) + 'Log.trc' 
FROM sys.traces WHERE is_default = 1; 

SELECT DatabaseName, StartTime, TextData 
FROM sys.fn_trace_gettable(@fn, DEFAULT) 
WHERE EventClass = 115 
 AND TextData LIKE '%RESTORE%'; -- since can't differentiate between backup/restore

我认为会使用服务器范围的触发器，并且任何创建数据库或还原数据库命令将立即跟随创建用户并在新创建或还原的数据库上拒绝 IUD：

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS

去

我在工作中提出了一个用例，其范围似乎相似，我被告知我必须允许某个帐户对新的和现有的数据库进行读取访问。我不想让用户能够在数据库和服务器级别进行更改。

在研究了如何去做之后，我发现从 SQL Server 2016 开始，您可以创建一个自定义服务器角色，然后您可以应用两个授权。

连接任何数据库并选择所有用户安全

在这两个授权之间，您授予对当前存在、新创建或已从给定实例上的备份中恢复的所有数据库的只读访问权限。拒绝写入是隐含的，因为您必须授予服务器角色允许写入的权限。

从我的脑海中，也许创建一个登录名并拒绝它的所有内容，只允许通过自定义存储过程进行选择/插入/更新/删除？