Como negar gravações em todos os bancos de dados do SQL Server (incluindo os novos e os restaurados)

Se você pudesse viver com um tempo finito (mínimo de 10 segundos) entre as execuções para capturar bancos de dados novos/restaurados, você poderia criar um trabalho agendado do SQL Server Agent e colocar algo assim em uma etapa de trabalho 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 ;

você não pode usar um gatilho de login e uma negação no DIU? Toda vez que o usuário faz login, acho que precisaria iterar os bancos de dados e adicionar os DENYs a qualquer banco de dados recém-adicionado.

Alguém pode tornar isso mais simples, mas aqui está minha abordagem:

1. Crie a função de servidor para "Administradores somente leitura" e atribua as seguintes permissões para permitir permissões em todo o servidor de recursos específicos, como a capacidade de ver a definição de todos os objetos e exibir o estado do servidor. Isso também cria um "bucket" para quais usuários você deseja atribuir às funções de banco de dados individuais:

   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. Crie um procedimento armazenado em [master] para criar/atualizar um grupo de usuários específico do banco de dados que concede leituras, mas nega gravações no banco de dados especificado. Estou usando um proc armazenado, pois você provavelmente precisará executar dentro do contexto do banco de dados especificado, de modo que o sql dinâmico é a primeira solução que vem à mente. Observe que esta solução força você a usar um nome de grupo específico, você pode alterá-lo para o que quiser.

EDIT: Conforme observado em outra solução, o procedimento armazenado precisa verificar se o banco de dados que está sendo adicionado/atualizado está no modo ONLINE. Se não, deve apenas fazer um loop e verificar a cada 5 segundos ou mais?

    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. Em seguida é adicionar um gatilho ddl para cobrir todos os eventos CREATE_DATABASE e ALTER_DATABASE. Esse gatilho precisa ser ajustado para lidar com cenários de alta disponibilidade, bem como alterações aleatórias na compatibilidade, modelo de recuperação etc. Além disso, o gatilho analisará o nome do banco de dados. Estou roubando de [Bob Pusateri] https://www.bobpusateri.com/archive/2018/09/a-tale-of-a-trigger/ para o manuseio de HA. Também não tenho certeza de como impedir que o gatilho seja acionado em atualizações não invasivas (como compatibilidade, modelo de recuperação, modo de usuário etc.). Acho que uma pessoa mais inteligente terá que lidar com isso. :)

   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. Agora, o acima abrange sempre que você adiciona um novo banco de dados ou atualiza um banco de dados na instância. O que você faz quando está apenas adicionando um novo login ao SERVER ROLE acima? Em cima da minha cabeça está criando outro proc armazenado (ou simplesmente alterando o proc armazenado acima para lidar com TODOS os bancos de dados locais e, em seguida, adicionando todos os usuários no SERVER ROLE a todos os bancos de dados que não são do sistema). Em seguida, junte isso com outro gatilho DDL que apenas analisa o evento ADD_SERVER_ROLE_MEMBER e, em seguida, apenas o SERVER ROLE específico "ReadOnlyAdmins".

Outros furos que identifiquei:

• Como lidar com o SQL Agent. Quero dizer, você pode criar uma função específica de banco de dados para o MSDB para cobrir coisas como verificar o status do trabalho, ver o log do trabalho etc.
• Como lidar com a replicação. Não tenho certeza de como lidar com essa situação específica em cima da minha cabeça. Precisaria pesquisar mais sobre isso.
• Isso poderia ser melhor servido por algum gerenciamento baseado em políticas? Mais uma vez, eu precisaria fazer mais pesquisas.

Obrigado.

Eu encontrei isso talvez este possa ajudar com a parte de restauração.

Executar procedimento armazenado automaticamente após qualquer evento 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

Acho que usaria um gatilho com escopo de servidor e qualquer comando Criar banco de dados ou Restaurar banco de dados seguiria imediatamente um usuário de criação e negaria o IUD no banco de dados recém-criado ou restaurado:

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

CRIAR TRIGGER ddl_trig_database
EM TODOS OS SERVIDOR
PARA CREATE_DATABASE
AS

VAI

Eu tive um caso de uso dentro de um trabalho que parece semelhante em escopo, no qual me disseram que eu tinha que permitir o acesso de leitura de uma determinada conta em bancos de dados novos e existentes. Eu não queria permitir que o usuário fizesse alterações nos níveis de banco de dados e servidor.

Depois de pesquisar como fazer isso, descobri que, a partir do SQL Server 2016, você pode criar uma função de servidor personalizado à qual pode aplicar duas concessões.

CONECTE QUALQUER BANCO DE DADOS e SELECIONE TODOS OS SEGURÁVEIS DO USUÁRIO

Entre essas duas concessões, você está concedendo acesso somente leitura a todos os bancos de dados que existem atualmente, recém-criados ou que foram restaurados do backup em uma determinada instância. Negar gravações está implícito porque você precisa conceder à função de servidor as permissões para permitir gravações.

Do topo da minha cabeça, talvez criar um login e negar tudo a ele e permitir apenas selecionar/inserir/atualizar/excluir por meio de procs armazenados personalizados?