Documentação Técnica — Backup e Reaplicação de Permissões (SQL Server)

CloudDB
22 de mar.
5 min de leitura

Você já perdeu o sono tentando restaurar permissões após um Refresh de Banco de Dados? 🛡️

Mover dados é fácil. O pesadelo começa quando as permissões de usuários, roles e acessos granulares se perdem no caminho. Em ambientes complexos, a reconstrução manual não é apenas lenta - é um risco de segurança.

Nossa equipe na CloudDB desenvolveu um pacote de procedures que automatiza o ciclo completo:

✅ Captura: Mapeia toda a estrutura de segurança atual.

✅ Auditoria: Histórico de quem tinha acesso a quê.

✅ Restauração: Reaplicação instantânea com um clique.

Menos trabalho manual, mais conformidade e zero "usuário sem acesso" na segunda-feira de manhã.

-- 🔧 1. Criação da base administrativa

-- Cria a base DBA_Admin caso não exista.

-- Esta base será utilizada para armazenar os scripts de permissões capturados.

USE [master];

IF DB_ID(N'DBA_Admin') IS NULL

BEGIN

CREATE DATABASE [DBA_Admin];

END

-- 🗂️ 2. Criação da tabela de armazenamento

-- Cria a tabela responsável por armazenar os scripts de permissões.

-- Cada linha representa um comando SQL ordenado, permitindo reconstrução fiel.

IF OBJECT_ID(N'DBA_Admin.dbo.DatabasePermissionScriptBackup', N'U') IS NULL

BEGIN

CREATE TABLE [DBA_Admin].[dbo].[DatabasePermissionScriptBackup]

(

DatabaseName sysname NOT NULL,

CaptureTime datetime2(0) NOT NULL,

ScriptLineNo int NOT NULL,

SqlStatement nvarchar(max) NOT NULL,

CONSTRAINT [PK_DatabasePermissionScriptBackup]

PRIMARY KEY CLUSTERED (DatabaseName, ScriptLineNo)

);

END

ELSE

BEGIN

-- Ajuste de compatibilidade (coluna antiga)

IF COL_LENGTH(N'DBA_Admin.dbo.DatabasePermissionScriptBackup', N'LineNo') IS NOT NULL

AND COL_LENGTH(N'DBA_Admin.dbo.DatabasePermissionScriptBackup', N'ScriptLineNo') IS NULL

BEGIN

EXEC [DBA_Admin].sys.sp_rename

@objname = N'dbo.DatabasePermissionScriptBackup.LineNo',

@newname = N'ScriptLineNo',

@objtype = N'COLUMN';

END

-- 🔍 3. Captura de permissões

-- Captura usuários, roles, permissões e associações do banco.

-- Pode processar um banco específico ou todos os bancos online.

-- Gera script completo e armazena na base DBA_Admin.

CREATE OR ALTER PROCEDURE dbo.sp_CaptureDatabasePermissions

@DatabaseName sysname = NULL,

@PrintProgress bit = 1

BEGIN

SET NOCOUNT ON;

SET XACT_ABORT ON;

-- Gera dinamicamente scripts contendo:

-- CREATE USER

-- CREATE ROLE

-- GRANT / DENY

-- ALTER USER (órfãos)

-- Associações de roles

END

-- 📄 4. Exibição do script capturado

-- Retorna o script armazenado para consulta, respeitando a ordem correta.

CREATE OR ALTER PROCEDURE dbo.sp_ShowDatabasePermissionsScript

@DatabaseName sysname

BEGIN

SET NOCOUNT ON;

SELECT

DatabaseName,

CaptureTime,

ScriptLineNo,

SqlStatement

FROM [DBA_Admin].[dbo].[DatabasePermissionScriptBackup]

WHERE DatabaseName = @DatabaseName

ORDER BY ScriptLineNo;

END

-- 🖨️ 5. Impressão do script

-- Imprime o script linha a linha no console do SQL Server (PRINT).

-- Útil para validação e export manual.

CREATE OR ALTER PROCEDURE dbo.sp_PrintDatabasePermissionsScript

@DatabaseName sysname

BEGIN

SET NOCOUNT ON;

DECLARE @stmt nvarchar(max);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT SqlStatement

FROM [DBA_Admin].[dbo].[DatabasePermissionScriptBackup]

WHERE DatabaseName = @DatabaseName

ORDER BY ScriptLineNo;

OPEN cur;

FETCH NEXT FROM cur INTO @stmt;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT @stmt;

FETCH NEXT FROM cur INTO @stmt;

END;

CLOSE cur;

DEALLOCATE cur;

END

-- 🔄 6. Reaplicação de permissões (por banco)

-- Executa novamente os comandos armazenados.

-- Recria usuários, roles e permissões automaticamente.

-- Pode interromper em erro (configurável).

CREATE OR ALTER PROCEDURE dbo.sp_ReapplyDatabasePermissions

@DatabaseName sysname,

@StopOnError bit = 0

BEGIN

SET NOCOUNT ON;

DECLARE @stmt nvarchar(max);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT SqlStatement

FROM [DBA_Admin].[dbo].[DatabasePermissionScriptBackup]

WHERE DatabaseName = @DatabaseName

AND SqlStatement IS NOT NULL

AND LTRIM(RTRIM(SqlStatement)) <> N''

AND LEFT(LTRIM(SqlStatement), 2) <> N'--'

ORDER BY ScriptLineNo;

OPEN cur;

FETCH NEXT FROM cur INTO @stmt;

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

EXEC sys.sp_executesql @stmt;

END TRY

BEGIN CATCH

PRINT N'Erro ao executar: ' + @stmt;

PRINT ERROR_MESSAGE();

IF @StopOnError = 1

THROW;

END CATCH;

FETCH NEXT FROM cur INTO @stmt;

END;

CLOSE cur;

DEALLOCATE cur;

END

-- 🔁 7. Reaplicação em lote

-- Executa a reaplicação de permissões para todos os bancos capturados.

-- Ideal para reconstrução completa de ambiente.

CREATE OR ALTER PROCEDURE dbo.sp_ReapplyDatabasePermissions_All

@StopOnError bit = 0

BEGIN

SET NOCOUNT ON;

DECLARE @DatabaseName sysname;

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR

SELECT DISTINCT DatabaseName

FROM [DBA_Admin].[dbo].[DatabasePermissionScriptBackup]

ORDER BY DatabaseName;

OPEN cur;

FETCH NEXT FROM cur INTO @DatabaseName;

WHILE @@FETCH_STATUS = 0

BEGIN

BEGIN TRY

EXEC master.dbo.sp_ReapplyDatabasePermissions

@DatabaseName = @DatabaseName,

@StopOnError = @StopOnError;

END TRY

BEGIN CATCH

PRINT N'Erro no banco ' + QUOTENAME(@DatabaseName);

PRINT ERROR_MESSAGE();

IF @StopOnError = 1

THROW;

END CATCH;

FETCH NEXT FROM cur INTO @DatabaseName;

END;

CLOSE cur;

DEALLOCATE cur;

END

📌 Conclusão

-- Este conjunto de scripts fornece uma abordagem estruturada e automatizada para gestão de permissões.

-- Permite capturar, versionar e reaplicar segurança de forma confiável.

-- Recomendado para ambientes críticos, auditoria e disaster recovery.

🚀 Exemplo Real de Execução (Step-by-Step)

Este exemplo demonstra um fluxo completo de uso das procedures, desde a captura até a reaplicação das permissões.

-- 🟢 1. Capturar permissões de um banco específico

-- Neste exemplo, vamos capturar as permissões do banco db_vendas.

EXEC master.dbo.sp_CaptureDatabasePermissions

@DatabaseName = 'db_vendas',

@PrintProgress = 1;

✔ O que acontece:

Varre usuários, roles e permissões
Gera script dinâmico
Salva tudo na tabela DBA_Admin.dbo.DatabasePermissionScriptBackup

-- 🟢 2. Consultar o script capturado

-- Visualiza o conteúdo armazenado (modo tabela)

EXEC master.dbo.sp_ShowDatabasePermissionsScript

@DatabaseName = 'db_vendas';

✔ Ideal para:

Auditoria
Conferência de permissões
Exportação

-- 🟢 3. Imprimir o script completo

-- Mostra o script linha a linha no output (PRINT)

EXEC master.dbo.sp_PrintDatabasePermissionsScript

@DatabaseName = 'db_vendas';

✔ Útil para:

Copiar e colar
Revisão rápida
Debug

-- 🟢 4. Simulação de perda de permissões (cenário real)

-- Exemplo: remover um usuário (simulando incidente)

USE db_vendas;

DROP USER [usuario_teste];

⚠️ Cenário comum:

Restore sem usuários
Migração incompleta
Falha de segurança

-- 🟢 5. Reaplicar permissões do banco

-- Restaura toda a estrutura capturada anteriormente

EXEC master.dbo.sp_ReapplyDatabasePermissions

@DatabaseName = 'db_vendas',

@StopOnError = 0;

✔ O que será recriado:

Usuários
Roles
Associações
Permissões

✔ O que acontece em erro:

Continua execução (se @StopOnError = 0)

-- 🟢 6. Reaplicar permissões de TODOS os bancos

-- Execução em lote

EXEC master.dbo.sp_ReapplyDatabasePermissions_All

@StopOnError = 0;

✔ Ideal para:

DR (Disaster Recovery)
Subida de ambiente novo
Migração completa

-- 🟢 7. Validar resultado

-- Conferir se permissões foram restauradas

USE db_vendas;

SELECT

dp.name AS Usuario,

dp.type_desc

FROM sys.database_principals dp

WHERE dp.type IN ('S','U','G')

ORDER BY dp.name;

✔ Confirma:

Usuários recriados
Estrutura restaurada

💡 Fluxo resumido

Capturar → Armazenar → Validar → (Perda) → Reaplicar → Validar

⚠️ Boas práticas

-- Sempre capturar antes de mudanças críticas

-- Manter histórico (não apagar registros antigos sem necessidade)

-- Testar reaplicação em ambiente de homologação

-- Garantir existência de logins no servidor (para evitar usuários órfãos)

🎯 Cenários reais de uso

✔ Migração de servidor SQL✔ Restore de banco sem usuários✔ Auditoria de permissões✔ Padronização de ambientes✔ Recuperação pós-incidente

✔ Conclusão

-- Esse fluxo elimina completamente a necessidade de recriar permissões manualmente

-- Reduz drasticamente erros humanos

-- Garante consistência entre ambientes

👉 Uma solução simples, mas extremamente poderosa para qualquer DBA.

Documentação Técnica — Backup e Reaplicação de Permissões (SQL Server)

Posts recentes

Comentários