Guia de integração de todos os recursos no ambiente SQL Compartilhado SkyNova

Fluxo Geral

https://suporte.skynova.com.br/hc/pt-br/articles/53453575368212-Permiss%C3%B5es-do-clientes-em-sua-pr%C3%B3pria-inst%C3%A2ncia-isolada-Conceito

           |
           +--> DATABASE LOCAL
           |      |-- tabelas / PK / DEFAULT / CHECK / índice
           |      |-- sequence / type / function / procedure / view / trigger
           |      |-- synonym
           |      |-- tabela de evidência das execuções
           |      `-- tabela de evidência do Linked Server
           |
           +--> DATABASE MAIL
           |      |-- profile
           |      |-- account SMTP real
           |      |-- acesso do login
           |      `-- envio real para <EMAIL_TESTE>
           |
           +--> LINKED SERVER
           |      `-- conexão real com servidor SQL remoto
           |
           +--> SQL SERVER AGENT
           |      |-- category
           |      |-- operator
           |      |-- Job principal
           |      |      |-- Step 1: processa database local
           |      |      |-- Step 2: consulta Linked Server real
           |      |      `-- Step 3: envia e-mail real via Database Mail
           |      |
           |      |-- Job de resposta de Alert
           |      |      |-- grava evidência local
           |      |      `-- envia e-mail real via Database Mail
           |      |
           |      |-- Job -> Operator
           |      `-- Alert -> Operator + ResponseJob
           |
           `--> TROCA DA PRÓPRIA SENHA SQL
                  |-- troca para senha temporária
                  |-- nova conexão real
                  |-- revalidação das integrações
                  `-- troca para senha final

Regra geral:

Substitua todos os valores entre <...> antes do bloco correspondente.
Não use objetos de produção com estes nomes.

Valores reais que você deve preencher

DATABASE LOCAL
    <MEU_DATABASE>

E-MAIL / SMTP
    <EMAIL_REMETENTE>
    <EMAIL_TESTE>
    <SMTP_HOST>
    <SMTP_PORT>
    
Se usar BASIC:
    <SMTP_USER>
    <SMTP_PASSWORD>

LINKED SERVER REAL
    <FQDN_OU_IP_REMOTO>
    <PORTA_REMOTA>
    <USUARIO_REMOTO>
    <SENHA_REMOTA_ATUAL>
    <DATABASE_REMOTO>

Para testar rotação real do Linked Server:
    <SENHA_REMOTA_NOVA>

SENHA DO LOGIN SQL ATUAL
    <SENHA_SQL_ATUAL>
    <SENHA_SQL_TEMPORARIA>
    <SENHA_SQL_FINAL>

Objetos que serão criados

DATABASE LOCAL
    dbo.LAB_INT_Cliente
    dbo.LAB_INT_Audit
    dbo.LAB_INT_Execucao
    dbo.LAB_INT_Remoto
    dbo.LAB_INT_seq
    dbo.LAB_INT_IdList
    dbo.LAB_INT_fn_Dobro
    dbo.LAB_INT_usp_Cliente_Get
    dbo.LAB_INT_usp_Processar
    dbo.LAB_INT_usp_ConsultarRemoto
    dbo.LAB_INT_vw_Ativos
    dbo.LAB_INT_tr_Audit
    dbo.LAB_INT_Cli
    IX_LAB_INT_Cliente_Nome

DATABASE MAIL
    Profile: LAB_INT_MAIL_PROFILE
    Account: LAB_INT_MAIL_ACCOUNT
    Account auxiliar: LAB_INT_MAIL_ACCOUNT_AUX --Ignorado esse passo

LINKED SERVER
    LAB_INT_LKS

SQL SERVER AGENT
    Category: LAB_INT_CATEGORY
    Category após rename: LAB_INT_CATEGORY_V2
    Operator: LAB_INT_OPERATOR
    Job principal: LAB_INT_JOB
    Job de resposta de Alert: LAB_INT_ALERT_RESPONSE
    Schedule: LAB_INT_DIARIO
    Schedule temporário: LAB_INT_SEMANAL
    Alert: LAB_INT_ALERT
    Alert após rename: LAB_INT_ALERT_V2

Valide se SQL Server Agent está acessível pelo tenant. Valor esperado (IsSqlAgentOperatorRole = 1)

USE [msdb];
GO
SELECT
   ORIGINAL_LOGIN() AS OriginalLogin,
   USER_NAME()      AS MsdbUser,
   IS_ROLEMEMBER(N'SQLAgentOperatorRole') AS IsSqlAgentOperatorRole;
GO

1. CRIAR A APLICAÇÃO LAB NO DATABASE LOCAL

USE [<MEU_DATABASE>];
GO

SELECT
    DB_NAME()                  AS DatabaseName,
    USER_NAME()                AS DatabaseUser,
    IS_ROLEMEMBER(N'db_owner') AS IsDbOwner;
GO

2. TABELA PRINCIPAL

CREATE TABLE dbo.LAB_INT_Cliente
(
    id int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_LAB_INT_Cliente PRIMARY KEY,
    nome varchar(100) NOT NULL,
    ativo bit NOT NULL
        CONSTRAINT DF_LAB_INT_Cliente_Ativo DEFAULT(1),
    criado_em datetime2(0) NOT NULL
        CONSTRAINT DF_LAB_INT_Cliente_Criado DEFAULT(SYSDATETIME()),
    CONSTRAINT CK_LAB_INT_Cliente_Nome CHECK(LEN(nome)>0)
);
GO

3. TABELA DE AUDITORIA

CREATE TABLE dbo.LAB_INT_Audit
(
   audit_id bigint IDENTITY(1,1) NOT NULL
       CONSTRAINT PK_LAB_INT_Audit PRIMARY KEY,
   cliente_id int NULL,
   operacao varchar(30) NOT NULL,
   login_execucao sysname NULL,
   data_hora datetime2(0) NOT NULL
       CONSTRAINT DF_LAB_INT_Audit_Data DEFAULT(SYSDATETIME())
);
GO

4. TABELA DE EVIDÊNCIA DE EXECUÇÕES

CREATE TABLE dbo.LAB_INT_Execucao
(
   execucao_id bigint IDENTITY(1,1) NOT NULL
       CONSTRAINT PK_LAB_INT_Execucao PRIMARY KEY,
   origem varchar(60) NOT NULL,
   detalhe nvarchar(4000) NULL,
   login_execucao sysname NULL,
   data_hora datetime2(0) NOT NULL
       CONSTRAINT DF_LAB_INT_Execucao_Data DEFAULT(SYSDATETIME())
);
GO

5. TABELA DE EVIDÊNCIA DO LINKED SERVER

CREATE TABLE dbo.LAB_INT_Remoto
(
   remoto_id bigint IDENTITY(1,1) NOT NULL
       CONSTRAINT PK_LAB_INT_Remoto PRIMARY KEY,
   remote_login sysname NULL,
   remote_database sysname NULL,
   remote_server sysname NULL,
   capturado_por sysname NULL,
   data_hora datetime2(0) NOT NULL
       CONSTRAINT DF_LAB_INT_Remoto_Data DEFAULT(SYSDATETIME())
);
GO

6. ÍNDICE

CREATE NONCLUSTERED INDEX IX_LAB_INT_Cliente_Nome
ON dbo.LAB_INT_Cliente(nome)
INCLUDE(ativo);
GO

7. SEQUENCE

CREATE SEQUENCE dbo.LAB_INT_seq
   AS bigint
   START WITH 1000
   INCREMENT BY 1;
GO

8. TABLE TYPE

CREATE TYPE dbo.LAB_INT_IdList AS TABLE
(
   id int NOT NULL PRIMARY KEY
);
GO

9. FUNCTION

CREATE OR ALTER FUNCTION dbo.LAB_INT_fn_Dobro(@Valor int)
RETURNS int
AS
BEGIN
   RETURN @Valor*2;
END;
GO

10. PROCEDURE DE CONSULTA

CREATE OR ALTER PROCEDURE dbo.LAB_INT_usp_Cliente_Get
   @Id int=NULL
AS
BEGIN
   SET NOCOUNT ON;
   SELECT id,nome,ativo,criado_em
   FROM dbo.LAB_INT_Cliente
   WHERE @Id IS NULL OR id=@Id
   ORDER BY id;
END;
GO

11. PROCEDURE DE PROCESSAMENTO USADA PELO JOB

CREATE OR ALTER PROCEDURE dbo.LAB_INT_usp_Processar
AS
BEGIN
   SET NOCOUNT ON;
   INSERT dbo.LAB_INT_Execucao(origem,detalhe,login_execucao)
   VALUES
   (
       N'JOB_LOCAL',
       N'Processamento local executado pelo SQL Server Agent',
       SUSER_SNAME()
   );
   SELECT
       COUNT_BIG(*) AS TotalClientes,
       SUM(CASE WHEN ativo=1 THEN 1 ELSE 0 END) AS ClientesAtivos,
       SUSER_SNAME() AS LoginExecucao,
       SYSDATETIME() AS DataHora
   FROM dbo.LAB_INT_Cliente;
END;
GO

12. VIEW

CREATE OR ALTER VIEW dbo.LAB_INT_vw_Ativos
AS
SELECT id,nome,criado_em
FROM dbo.LAB_INT_Cliente
WHERE ativo=1;
GO

13. TRIGGER

CREATE OR ALTER TRIGGER dbo.LAB_INT_tr_Audit
ON dbo.LAB_INT_Cliente
AFTER INSERT
AS
BEGIN
   SET NOCOUNT ON;
   INSERT dbo.LAB_INT_Audit(cliente_id,operacao,login_execucao)
   SELECT id,N'INSERT',SUSER_SNAME()
   FROM inserted;
END;
GO

14. SYNONYM

CREATE SYNONYM dbo.LAB_INT_Cli
FOR dbo.LAB_INT_Cliente;
GO

15. DADOS REAIS

INSERT dbo.LAB_INT_Cliente(nome)
VALUES
   (N'Cliente Integrado 1'),
   (N'Cliente Integrado 2');
GO

16. VALIDAR

SELECT * FROM dbo.LAB_INT_Cliente ORDER BY id;
SELECT * FROM dbo.LAB_INT_Audit ORDER BY audit_id;
SELECT * FROM dbo.LAB_INT_vw_Ativos ORDER BY id;
SELECT * FROM dbo.LAB_INT_Cli ORDER BY id;
SELECT dbo.LAB_INT_fn_Dobro(10) AS Dobro;
EXEC dbo.LAB_INT_usp_Cliente_Get;
SELECT NEXT VALUE FOR dbo.LAB_INT_seq AS ProximoSequence;
GO

/*
RESULTADO ESPERADO:
- 2 linhas em LAB_INT_Cliente;
- 2 linhas em LAB_INT_Audit;
- função retorna 20;
- procedure/view/synonym consultáveis;
- sequence retorna valor >= 1000.
*/

*MODIFICAR / ADICIONAR / REMOVER OBJETOS DO DATABASE

17. ADICIONAR COLUNA

ALTER TABLE dbo.LAB_INT_Cliente
ADD email varchar(255) NULL;
GO
UPDATE dbo.LAB_INT_Cliente
SET email=CONCAT(N'cliente',id,N'@lab.local');
GO

18. ALTERAR PROCEDURE

CREATE OR ALTER PROCEDURE dbo.LAB_INT_usp_Cliente_Get
   @Id int=NULL
AS
BEGIN
   SET NOCOUNT ON;
   SELECT id,nome,email,ativo,criado_em
   FROM dbo.LAB_INT_Cliente
   WHERE @Id IS NULL OR id=@Id
   ORDER BY id;
END;
GO

19. ALTERAR VIEW

CREATE OR ALTER VIEW dbo.LAB_INT_vw_Ativos
AS
SELECT id,nome,email,criado_em
FROM dbo.LAB_INT_Cliente
WHERE ativo=1;
GO

20. ALTERAR ÍNDICE

CREATE NONCLUSTERED INDEX IX_LAB_INT_Cliente_Nome
ON dbo.LAB_INT_Cliente(nome)
INCLUDE(ativo,email)
WITH (DROP_EXISTING=ON);
GO

21. ADICIONAR CONSTRAINT

ALTER TABLE dbo.LAB_INT_Cliente
ADD CONSTRAINT CK_LAB_INT_Cliente_Email
CHECK(email IS NULL OR email LIKE N'%@%');
GO

22. REMOVER CONSTRAINT NOVA

ALTER TABLE dbo.LAB_INT_Cliente
DROP CONSTRAINT CK_LAB_INT_Cliente_Email;
GO
--Validar
EXEC dbo.LAB_INT_usp_Cliente_Get;
SELECT * FROM dbo.LAB_INT_vw_Ativos;
GO

*Criar e testar Database Mail com SMTP de envio

USE [msdb];
GO

23. CRIAR PROFILE

EXEC dbo.usp_TenantDatabaseMail_SaveProfile
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @Description=N'LAB integrado - profile SMTP real',
   @NewProfileName=NULL;
GO

24A. CRIAR ACCOUNT — SMTP ANONYMOUS

--Use este bloco quando o relay SMTP aceita o IP da instância sem usuário/senha.
EXEC dbo.usp_TenantDatabaseMail_SaveAccount
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @AccountName=N'LAB_INT_MAIL_ACCOUNT',
   @EmailAddress=N'<EMAIL_REMETENTE>',
   @DisplayName=N'SkyNova SQL LAB Integrado',
   @ReplyToAddress=N'<EMAIL_TESTE>',
   @Description=N'LAB integrado - SMTP real',
   @MailServerName=N'<SMTP_HOST>',
   @Port=<SMTP_PORT>,
   @EnableSsl=0,
   @AuthenticationMode=N'ANONYMOUS',
   @UserName=NULL,
   @Password=NULL,
   @SequenceNumber=1,
   @NewAccountName=NULL;
GO

24B. ALTERNATIVA — SMTP BASIC

/*
NÃO execute este bloco se já executou 24A. Use este bloco no lugar do 24A 
quando seu SMTP exigir usuário/senha.
*/

EXEC dbo.usp_TenantDatabaseMail_SaveAccount
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @AccountName=N'LAB_INT_MAIL_ACCOUNT',
   @EmailAddress=N'<EMAIL_REMETENTE>',
   @DisplayName=N'SkyNova SQL LAB Integrado',
   @ReplyToAddress=N'<EMAIL_TESTE>',
   @Description=N'LAB integrado - SMTP real autenticado',
   @MailServerName=N'<SMTP_HOST>',
   @Port=<SMTP_PORT>,
   @EnableSsl=1,
   @AuthenticationMode=N'BASIC',
   @UserName=N'<SMTP_USER>',
   @Password=N'<SMTP_PASSWORD>',
   @SequenceNumber=1,
   @NewAccountName=NULL;
GO

25. AUTORIZAR O LOGIN REAL DO TENANT

DECLARE @LoginAtual sysname=ORIGINAL_LOGIN();
EXEC dbo.usp_TenantDatabaseMail_SetProfileAccess
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @LoginName=@LoginAtual,
   @IsEnabled=1,
   @IsDefault=1;
GO

26. CONSULTAR PROFILE / ACCOUNT

EXEC dbo.usp_TenantDatabaseMail_List;
EXEC dbo.usp_TenantDatabaseMail_QueueStatus;
GO
SELECT profile_id,name,description
FROM dbo.sysmail_profile
WHERE name=N'LAB_INT_MAIL_PROFILE';
SELECT account_id,name,email_address,display_name
FROM dbo.sysmail_account
WHERE name=N'LAB_INT_MAIL_ACCOUNT';
GO

27. ENVIO REAL Nº 1 — WRAPPER

EXEC dbo.usp_TenantDatabaseMail_Send
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @recipients=N'<EMAIL_TESTE>',
   @subject=N'[SkyNova LAB] 01 - Database Mail direto',
   @body=N'Envio real do Database Mail criado pelo tenant no LAB integrado.',
   @body_format=N'TEXT',
   @copy_recipients=NULL,
   @blind_copy_recipients=NULL,
   @importance=N'NORMAL';
GO

28. OBSERVAR O ENVIO REAL

EXEC dbo.usp_TenantDatabaseMail_QueueStatus;
EXEC dbo.usp_TenantDatabaseMail_SentItems
   @TopRows=50,
   @OnlyFailed=0;
EXEC dbo.usp_TenantDatabaseMail_EventLog
   @TopRows=50;
GO

*MODIFICAR DATABASE MAIL + ADICIONAR/REMOVER ACESSO/CONTA

USE [msdb];
GO

29. MODIFICAR PROFILE

EXEC dbo.usp_TenantDatabaseMail_SaveProfile
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @Description=N'LAB integrado - profile alterado e validado a quente',
   @NewProfileName=NULL;
GO

30. MODIFICAR ACCOUNT PRINCIPAL — ANONYMOUS

--Se você está usando ANONYMOUS, execute:
EXEC dbo.usp_TenantDatabaseMail_SaveAccount
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @AccountName=N'LAB_INT_MAIL_ACCOUNT',
   @EmailAddress=N'<EMAIL_REMETENTE>',
   @DisplayName=N'SkyNova SQL LAB Integrado - Alterado',
   @ReplyToAddress=N'<EMAIL_TESTE>',
   @Description=N'LAB integrado - account alterado',
   @MailServerName=N'<SMTP_HOST>',
   @Port=<SMTP_PORT>,
   @EnableSsl=0,
   @AuthenticationMode=N'ANONYMOUS',
   @UserName=NULL,
   @Password=NULL,
   @SequenceNumber=1,
   @NewAccountName=NULL;
GO

/*
Se está usando BASIC, repita o SaveAccount acima mantendo @EnableSsl=1,
@AuthenticationMode=N'BASIC', <SMTP_USER> e <SMTP_PASSWORD> reais.
*/

EXEC dbo.usp_TenantDatabaseMail_SaveAccount
    @ProfileName=N'LAB_INT_MAIL_PROFILE',
    @AccountName=N'LAB_INT_MAIL_ACCOUNT',
    @EmailAddress=N'sender@dominio.com.br',
    @DisplayName=N'SkyNova SQL LAB Integrado',
    @ReplyToAddress=N'remetente@dominio.com.br',
    @Description=N'LAB integrado - SMTP real autenticado',
    @MailServerName=N'smtp.dominio.com.br',
    @Port=587,
    @EnableSsl=1,
    @AuthenticationMode=N'BASIC',
    @UserName=N'sender@dominio.com.br',
    @Password=N'<SMTP_PASSWORD>',
    @SequenceNumber=1,
    @NewAccountName=NULL;
GO

31. REMOVER E RECRIAR ACESSO DO LOGIN ATUAL

DECLARE @LoginAtual sysname=ORIGINAL_LOGIN();
EXEC dbo.usp_TenantDatabaseMail_SetProfileAccess
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @LoginName=@LoginAtual,
   @IsEnabled=0;
GO

--Agora reative:
DECLARE @LoginAtual sysname=ORIGINAL_LOGIN();
EXEC dbo.usp_TenantDatabaseMail_SetProfileAccess
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @LoginName=@LoginAtual,
   @IsEnabled=1,
   @IsDefault=1;
GO

--Validar
EXEC dbo.usp_TenantDatabaseMail_List;
GO

32. CONSULTAR PROFILE / ACCOUNT

EXEC dbo.usp_TenantDatabaseMail_List;
EXEC dbo.usp_TenantDatabaseMail_QueueStatus;
GO
SELECT profile_id,name,description
FROM dbo.sysmail_profile
WHERE name=N'LAB_INT_MAIL_PROFILE';
SELECT account_id,name,email_address,display_name
FROM dbo.sysmail_account
WHERE name=N'LAB_INT_MAIL_ACCOUNT';
GO

33. ENVIO REAL Nº 1 — WRAPPER

EXEC dbo.usp_TenantDatabaseMail_Send
   @ProfileName=N'LAB_INT_MAIL_PROFILE',
   @recipients=N'<EMAIL_TESTE>',
   @subject=N'[SkyNova LAB] 01 - Database Mail direto',
   @body=N'Envio real do Database Mail criado pelo tenant no LAB integrado.',
   @body_format=N'TEXT',
   @copy_recipients=NULL,
   @blind_copy_recipients=NULL,
   @importance=N'NORMAL';
GO

34. OBSERVAR O ENVIO REAL

EXEC dbo.usp_TenantDatabaseMail_QueueStatus;
EXEC dbo.usp_TenantDatabaseMail_SentItems
   @TopRows=50,
   @OnlyFailed=0;
EXEC dbo.usp_TenantDatabaseMail_EventLog
   @TopRows=50;
GO

*CRIAR LINKED SERVER

USE [master];
GO
EXEC dbo.usp_TenantLinkedServer_Create
   @ServerName=N'LAB_INT_LKS',
   @DataSource=N'<FQDN_OU_IP_REMOTO>,<PORTA_REMOTA>',
   @RemoteUser=N'<USUARIO_REMOTO>',
   @RemotePassword=N'<SENHA_REMOTA_ATUAL>',
   @Catalog=NULL,
 --@Catalog=N'<DATABASE_REMOTO>',
   @TestConnection=1;
GO

35. LISTAR E TESTAR

EXEC dbo.usp_TenantLinkedServer_List;
EXEC dbo.usp_TenantLinkedServer_Test
   @ServerName=N'LAB_INT_LKS';
GO

36. CONSULTA REAL DE IDENTIDADE REMOTA

SELECT *
FROM OPENQUERY
(
   [LAB_INT_LKS],
   'SELECT
        SUSER_SNAME() AS RemoteLogin,
        DB_NAME()     AS RemoteDatabase,
        @@SERVERNAME  AS RemoteServer'
);
GO

/*
RESULTADO ESPERADO:
- RemoteLogin = <USUARIO_REMOTO> ou login efetivamente mapeado no destino;
- RemoteDatabase = <DATABASE_REMOTO>;
- RemoteServer = servidor SQL remoto real.
*/

37. CONSULTA REAL AO DATABASE REMOTO

SELECT TOP (10)
   name,
   object_id,
   create_date
FROM [LAB_INT_LKS].[<DATABASE_REMOTO>].[sys].[tables]
ORDER BY name;
GO

*INTEGRAR O LINKED SERVER AO DATABASE LOCAL

USE [<MEU_DATABASE>];
GO
CREATE OR ALTER PROCEDURE dbo.LAB_INT_usp_ConsultarRemoto
AS
BEGIN
   SET NOCOUNT ON;
   INSERT dbo.LAB_INT_Remoto
   (
       remote_login,
       remote_database,
       remote_server,
       capturado_por
   )
   SELECT
       R.RemoteLogin,
       R.RemoteDatabase,
       R.RemoteServer,
       SUSER_SNAME()
   FROM OPENQUERY
   (
       [LAB_INT_LKS],
       'SELECT
            SUSER_SNAME() AS RemoteLogin,
            DB_NAME()     AS RemoteDatabase,
            @@SERVERNAME  AS RemoteServer'
   ) AS R;
   SELECT TOP (1) *
   FROM dbo.LAB_INT_Remoto
   ORDER BY remoto_id DESC;
END;
GO

--Execute manualmente:
EXEC dbo.LAB_INT_usp_ConsultarRemoto;
GO
Valide:
SELECT *
FROM dbo.LAB_INT_Remoto
ORDER BY remoto_id DESC;
GO

--RESULTADO ESPERADO: Ao menos uma linha real retornada pelo Linked Server.

38. CRIAR CATEGORIA DO SQL SERVER AGENT

USE [msdb];
GO
EXEC dbo.usp_TenantSqlAgentCategory_Create
   @CategoryName=N'LAB_INT_CATEGORY',
   @CategoryClass=N'JOB';
GO
SELECT
   category_id,
   category_class,
   category_type,
   name
FROM dbo.syscategories
WHERE category_class=1
 AND name=N'LAB_INT_CATEGORY';
GO

39. CRIAR OPERATOR

EXEC dbo.usp_TenantSqlAgentOperator_Create
   @OperatorName=N'LAB_INT_OPERATOR',
   @EmailAddress=N'<EMAIL_TESTE>',
   @Enabled=1;
GO
EXEC dbo.usp_TenantSqlAgentOperator_List;
GO
SELECT
   id,
   name,
   enabled,
   email_address
FROM dbo.sysoperators
WHERE name=N'LAB_INT_OPERATOR';
GO

40. MODIFICAR OPERATOR

EXEC dbo.usp_TenantSqlAgentOperator_Update
   @OperatorName=N'LAB_INT_OPERATOR',
   @EmailAddress=N'<EMAIL_TESTE>',
   @Enabled=0;
GO
SELECT name,enabled,email_address
FROM dbo.sysoperators
WHERE name=N'LAB_INT_OPERATOR';
GO
EXEC dbo.usp_TenantSqlAgentOperator_Update
   @OperatorName=N'LAB_INT_OPERATOR',
   @EmailAddress=N'<EMAIL_TESTE>',
   @Enabled=1;
GO

41. CRIAR JOB PRINCIPAL — DATABASE + LINKED SERVER + DATABASE MAIL

USE [msdb];
GO
EXEC dbo.sp_add_job
   @job_name=N'LAB_INT_JOB',
   @enabled=1,
   @description=N'LAB integrado real - database + linked server + mail',
   @category_name=N'LAB_INT_CATEGORY';
GO

STEP 1 — PROCESSAR DATABASE LOCAL
------------------------------
EXEC dbo.sp_add_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_name=N'01 - Processar database local',
   @step_id=1,
   @subsystem=N'TSQL',
   @database_name=N'<MEU_DATABASE>',
   @command=N'
SET NOCOUNT ON;
EXEC dbo.LAB_INT_usp_Processar;',
   @on_success_action=3,
   @on_fail_action=2;
GO

STEP 2 — CONSULTAR LINKED SERVER
------------------------------
EXEC dbo.sp_add_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_name=N'02 - Consultar Linked Server',
   @step_id=2,
   @subsystem=N'TSQL',
   @database_name=N'<MEU_DATABASE>',
   @command=N'
SET NOCOUNT ON;
EXEC dbo.LAB_INT_usp_ConsultarRemoto;',
   @on_success_action=3,
   @on_fail_action=2;
GO

STEP 3 — ENVIAR E-MAIL REAL PELO DATABASE MAIL
------------------------------
Este Step usa o Database Mail nativo com o profile criado pelo próprio tenant.
EXEC dbo.sp_add_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_name=N'03 - Enviar email de sucesso',
   @step_id=3,
   @subsystem=N'TSQL',
   @database_name=N'msdb',
   @command=N'
EXEC msdb.dbo.sp_send_dbmail
   @profile_name=N''LAB_INT_MAIL_PROFILE'',
   @recipients=N''<EMAIL_TESTE>'',
   @subject=N''[SkyNova LAB] 02 - Job integrado concluído'',
   @body=N''Job principal executou database local, Linked Server e Database Mail.'',
   @body_format=N''TEXT'',
   @importance=N''NORMAL'';',
   @on_success_action=1,
   @on_fail_action=2;
GO

VINCULAR JOB À INSTÂNCIA LOCAL
------------------------------
EXEC dbo.sp_add_jobserver
   @job_name=N'LAB_INT_JOB',
   @server_name=N'(LOCAL)';
GO

VALIDAR JOB E STEPS
------------------------------
SELECT
   j.name AS JobName,
   j.enabled,
   SUSER_SNAME(j.owner_sid) AS OwnerLogin,
   c.name AS CategoryName,
   j.description
FROM dbo.sysjobs AS j
LEFT JOIN dbo.syscategories AS c
 ON c.category_id=j.category_id
WHERE j.name=N'LAB_INT_JOB';
SELECT
   j.name AS JobName,
   s.step_id,
   s.step_name,
   s.subsystem,
   s.database_name,
   s.on_success_action,
   s.on_fail_action,
   s.command
FROM dbo.sysjobs AS j
JOIN dbo.sysjobsteps AS s
 ON s.job_id=j.job_id
WHERE j.name=N'LAB_INT_JOB'
ORDER BY s.step_id;
GO

CRIAR SCHEDULE PRINCIPAL
------------------------------
EXEC dbo.sp_add_jobschedule
   @job_name=N'LAB_INT_JOB',
   @name=N'LAB_INT_DIARIO',
   @enabled=0,
   @freq_type=4,
   @freq_interval=1,
   @freq_subday_type=1,
   @active_start_time=230000;
GO
SELECT
   j.name AS JobName,
   s.name AS ScheduleName,
   s.enabled,
   s.freq_type,
   s.freq_interval,
   s.freq_subday_type,
   s.active_start_time
FROM dbo.sysjobs AS j
JOIN dbo.sysjobschedules AS js
 ON js.job_id=j.job_id
JOIN dbo.sysschedules AS s
 ON s.schedule_id=js.schedule_id
WHERE j.name=N'LAB_INT_JOB';
GO

INTEGRAR JOB -> OPERATOR
------------------------------
--Configure o Operator para notificação nativa de FALHA do Job:
EXEC dbo.usp_TenantSqlAgentOperator_SetJobNotification
   @JobName=N'LAB_INT_JOB',
   @OperatorName=N'LAB_INT_OPERATOR',
   @NotifyLevelEmail=2;
GO
Valide:
SELECT
   j.name AS JobName,
   o.name AS OperatorName,
   j.notify_level_email,
   j.notify_level_page,
   j.notify_level_netsend
FROM dbo.sysjobs AS j
LEFT JOIN dbo.sysoperators AS o
 ON o.id=j.notify_email_operator_id
WHERE j.name=N'LAB_INT_JOB';
GO
RESULTADO ESPERADO:
   OperatorName = LAB_INT_OPERATOR
   notify_level_email = 2

EXECUTAR O JOB PRINCIPAL MANUALMENTE
------------------------------
EXEC dbo.sp_start_job
   @job_name=N'LAB_INT_JOB';
GO

--Aguarde a conclusão e consulte:
SELECT TOP (30)
   j.name,
   h.step_id,
   h.step_name,
   h.run_status,
   h.run_date,
   h.run_time,
   h.run_duration,
   h.message
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j
 ON j.job_id=h.job_id
WHERE j.name=N'LAB_INT_JOB'
ORDER BY h.instance_id DESC;
GO

--Valide as mudanças realizadas locais:
USE [<MEU_DATABASE>];
GO
SELECT *
FROM dbo.LAB_INT_Execucao
ORDER BY execucao_id DESC;
SELECT *
FROM dbo.LAB_INT_Remoto
ORDER BY remoto_id DESC;
GO

42. MODIFICAR JOB / NOTIFICAÇÃO

USE [msdb];
GO
EXEC dbo.sp_update_job
   @job_name=N'LAB_INT_JOB',
   @enabled=0,
   @description=N'LAB integrado real - descrição alterada';
GO
SELECT name,enabled,description
FROM dbo.sysjobs
WHERE name=N'LAB_INT_JOB';
GO
EXEC dbo.sp_update_job
   @job_name=N'LAB_INT_JOB',
   @enabled=1;
GO

43. MODIFICAR NÍVEL DA NOTIFICAÇÃO

EXEC dbo.usp_TenantSqlAgentOperator_SetJobNotification
   @JobName=N'LAB_INT_JOB',
   @OperatorName=N'LAB_INT_OPERATOR',
   @NotifyLevelEmail=3;
GO

SELECT name,notify_level_email
FROM dbo.sysjobs
WHERE name=N'LAB_INT_JOB';
GO
--RESULTADO ESPERADO: notify_level_email = 3

--Volte para FALHA:
EXEC dbo.usp_TenantSqlAgentOperator_SetJobNotification
   @JobName=N'LAB_INT_JOB',
   @OperatorName=N'LAB_INT_OPERATOR',
   @NotifyLevelEmail=2;
GO

44. ADICIONAR / REMOVER STEP E SCHEDULE

USE [msdb];
GO
EXEC dbo.sp_update_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_id=3,
   @on_success_action=3;
GO
EXEC dbo.sp_add_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_name=N'04 - Step temporário',
   @step_id=4,
   @subsystem=N'TSQL',
   @database_name=N'<MEU_DATABASE>',
   @command=N'SELECT N''STEP TEMPORÁRIO EXECUTÁVEL'' AS Resultado;',
   @on_success_action=1,
   @on_fail_action=2;
GO
SELECT step_id,step_name,on_success_action,on_fail_action
FROM dbo.sysjobsteps
WHERE job_id=(SELECT job_id FROM dbo.sysjobs WHERE name=N'LAB_INT_JOB')
ORDER BY step_id;
GO

45. REMOVER STEP 4

EXEC dbo.sp_delete_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_id=4;
GO
EXEC dbo.sp_update_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_id=3,
   @on_success_action=1;
GO

46. ADICIONAR SCHEDULE SEMANAL

EXEC dbo.sp_add_jobschedule
   @job_name=N'LAB_INT_JOB',
   @name=N'LAB_INT_SEMANAL',
   @enabled=0,
   @freq_type=8,
   @freq_interval=2,
   @freq_recurrence_factor=1,
   @freq_subday_type=1,
   @active_start_time=040000;
GO

47. MODIFICAR SCHEDULE PRINCIPAL

EXEC dbo.sp_update_schedule
   @name=N'LAB_INT_DIARIO',
   @enabled=0,
   @active_start_time=231500;
GO

48. REMOVER SCHEDULE SEMANAL

EXEC dbo.sp_detach_schedule
   @job_name=N'LAB_INT_JOB',
   @schedule_name=N'LAB_INT_SEMANAL';
GO
IF EXISTS (SELECT 1 FROM dbo.sysschedules WHERE name=N'LAB_INT_SEMANAL')
BEGIN
   EXEC dbo.sp_delete_schedule
       @schedule_name=N'LAB_INT_SEMANAL',
       @force_delete=0;
END;
GO

49. RENOMEAR CATEGORIA E VALIDAR OS VÍNCULOS

EXEC dbo.usp_TenantSqlAgentCategory_Update
   @CategoryName=N'LAB_INT_CATEGORY',
   @NewCategoryName=N'LAB_INT_CATEGORY_V2',
   @CategoryClass=N'JOB';
GO
SELECT
   j.name AS JobName,
   c.name AS CategoryName
FROM dbo.sysjobs AS j
LEFT JOIN dbo.syscategories AS c
 ON c.category_id=j.category_id
WHERE j.name=N'LAB_INT_JOB';
GO
--RESULTADO ESPERADO: CategoryName = LAB_INT_CATEGORY_V2

50. TESTE DE FALHA REAL CONTROLADA — JOB + MAIL + OPERATOR

/*
O Step 2 será temporariamente substituído. Ele enviará um e-mail real de falha e
em seguida encerrará o Job com erro. O Job continua associado ao Operator.
*/

USE [msdb];
GO

EXEC dbo.sp_update_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_id=2,
   @database_name=N'msdb',
   @command=N'
EXEC msdb.dbo.sp_send_dbmail
   @profile_name=N''LAB_INT_MAIL_PROFILE'',
   @recipients=N''<EMAIL_TESTE>'',
   @subject=N''[SkyNova LAB] 03 - Job falha controlada'',
   @body=N''O Job integrado executará uma falha controlada após este e-mail.'',
   @body_format=N''TEXT'',
   @importance=N''HIGH'';
   
THROW 51000, ''LAB_INT - falha controlada do Job'', 1;',
   @on_success_action=3,
   @on_fail_action=2;
GO

--Inicie o job
EXEC dbo.sp_start_job
   @job_name=N'LAB_INT_JOB';
GO

--Aguarde a conclusão e consulte:
SELECT TOP (30)
   j.name,
   h.step_id,
   h.step_name,
   h.run_status,
   h.message,
   h.run_date,
   h.run_time
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j
 ON j.job_id=h.job_id
WHERE j.name=N'LAB_INT_JOB'
ORDER BY h.instance_id DESC;
GO

--Valide também o vínculo do Operator:
SELECT
   j.name,
   o.name AS OperatorName,
   j.notify_level_email
FROM dbo.sysjobs AS j
LEFT JOIN dbo.sysoperators AS o
 ON o.id=j.notify_email_operator_id
WHERE j.name=N'LAB_INT_JOB';
GO

--Restaure o Step 2 real:
EXEC dbo.sp_update_jobstep
   @job_name=N'LAB_INT_JOB',
   @step_id=2,
   @database_name=N'<MEU_DATABASE>',
   @command=N'
SET NOCOUNT ON;
EXEC dbo.LAB_INT_usp_ConsultarRemoto;',
   @on_success_action=3,
   @on_fail_action=2;
GO

51. CRIAR JOB DE RESPOSTA DO ALERT

--Este Job não possui Schedule. Ele será iniciado pelo Alert.
USE [msdb];
GO

-->
EXEC dbo.sp_add_job
   @job_name=N'LAB_INT_ALERT_RESPONSE',
   @enabled=1,
   @description=N'LAB integrado - resposta real disparada pelo Alert',
   @category_name=N'LAB_INT_CATEGORY_V2';
GO

-->
EXEC dbo.sp_add_jobstep
   @job_name=N'LAB_INT_ALERT_RESPONSE',
   @step_name=N'01 - Registrar e enviar email do Alert',
   @step_id=1,
   @subsystem=N'TSQL',
   @database_name=N'<MEU_DATABASE>',
   @command=N'
SET NOCOUNT ON;
INSERT dbo.LAB_INT_Execucao(origem,detalhe,login_execucao)
VALUES
(
   N''ALERT_RESPONSE'',
   N''Job de resposta disparado pelo SQL Server Agent Alert'',
   SUSER_SNAME()
);
EXEC msdb.dbo.sp_send_dbmail
   @profile_name=N''LAB_INT_MAIL_PROFILE'',
   @recipients=N''<EMAIL_TESTE>'',
   @subject=N''[SkyNova LAB] 04 - Alert disparado'',
   @body=N''O Alert de performance disparou o Job LAB_INT_ALERT_RESPONSE.'',
   @body_format=N''TEXT'',
   @importance=N''HIGH'';',
   @on_success_action=1,
   @on_fail_action=2;
GO

-->
EXEC dbo.sp_add_jobserver
   @job_name=N'LAB_INT_ALERT_RESPONSE',
   @server_name=N'(LOCAL)';
GO

-->
SELECT
   j.name,
   j.enabled,
   SUSER_SNAME(j.owner_sid) AS OwnerLogin,
   c.name AS CategoryName
FROM dbo.sysjobs AS j
LEFT JOIN dbo.syscategories AS c
 ON c.category_id=j.category_id
WHERE j.name=N'LAB_INT_ALERT_RESPONSE';
GO

52. CRIAR ALERT REAL DE PERFORMANCE

/*
O Alert será criado DESABILITADO, associado ao Job de resposta e ao Operator, e
somente então habilitado.
A condição usada é "User Connections > 0". Como você está conectado para executar
o LAB, a condição deve se tornar verdadeira. O nome do objeto de performance é
montado de acordo com instância default ou instância nomeada.
*/

USE [msdb];
GO

DECLARE @InstanceName sysname =
    CONVERT(sysname, SERVERPROPERTY(N'InstanceName'));

DECLARE @PerformanceCondition nvarchar(512);

IF @InstanceName IS NULL
BEGIN
    SET @PerformanceCondition =
        N'SQLServer:General Statistics|User Connections||>|0';
END
ELSE
BEGIN
    SET @PerformanceCondition =
        N'MSSQL$' + @InstanceName +
        N':General Statistics|User Connections||>|0';
END;

SELECT
    SERVERPROPERTY(N'ServerName')   AS ServerName,
    SERVERPROPERTY(N'InstanceName') AS InstanceName,
    @PerformanceCondition           AS PerformanceCondition;

EXEC msdb.dbo.usp_TenantSqlAgentAlert_Create
    @AlertName                 = N'LAB_INT_ALERT',
    @Enabled                   = 0,
    @PerformanceCondition      = @PerformanceCondition,
    @DelayBetweenResponses     = 3600,
    @NotificationMessage       = N'LAB integrado - User Connections maior que zero',
    @IncludeEventDescriptionIn = 1,
    @ResponseJobName           = N'LAB_INT_ALERT_RESPONSE';
GO

ASSOCIAR ALERT AO OPERATOR
------------------------------
EXEC dbo.usp_TenantSqlAgentAlert_SetNotification
   @AlertName=N'LAB_INT_ALERT',
   @OperatorName=N'LAB_INT_OPERATOR',
   @NotificationMethod=1;
GO

CONSULTAR ALERT
------------------------------
EXEC dbo.usp_TenantSqlAgentAlert_List
   @AlertName=N'LAB_INT_ALERT';
GO
SELECT
   a.name,
   a.enabled,
   a.performance_condition,
   a.delay_between_responses,
   a.occurrence_count,
   a.last_occurrence_date,
   a.last_occurrence_time,
   j.name AS ResponseJobName
FROM dbo.sysalerts AS a
LEFT JOIN dbo.sysjobs AS j
 ON j.job_id=a.job_id
WHERE a.name=N'LAB_INT_ALERT';
GO

53. MODIFICAR ALERT + REMOVER/RECRIAR NOTIFICAÇÃO

--RENOMEAR ALERT
EXEC dbo.usp_TenantSqlAgentAlert_Update
   @AlertName=N'LAB_INT_ALERT',
   @NewAlertName=N'LAB_INT_ALERT_V2',
   @Enabled=0,
   @DelayBetweenResponses=3600,
   @NotificationMessage=N'LAB integrado - Alert alterado e pronto para disparo',
   @ResponseJobName=N'LAB_INT_ALERT_RESPONSE';
GO

54. REMOVER NOTIFICAÇÃO DO OPERATOR

EXEC dbo.usp_TenantSqlAgentAlert_SetNotification
   @AlertName=N'LAB_INT_ALERT_V2',
   @OperatorName=N'LAB_INT_OPERATOR',
   @NotificationMethod=1,
   @Remove=1;
GO

55. RECRIAR NOTIFICAÇÃO DO OPERATOR

EXEC dbo.usp_TenantSqlAgentAlert_SetNotification
   @AlertName=N'LAB_INT_ALERT_V2',
   @OperatorName=N'LAB_INT_OPERATOR',
   @NotificationMethod=1;
GO

56. HABILITAR ALERT

EXEC dbo.usp_TenantSqlAgentAlert_Update
   @AlertName=N'LAB_INT_ALERT_V2',
   @Enabled=1;
GO

*VALIDAR DISPARO REAL DO ALERT

/*
Mantenha ao menos esta sessão conectada. Aguarde a amostragem do SQL Server Agent e
consulte o Alert e o Job de resposta.
*/

USE [msdb];
GO
SELECT
   name,
   enabled,
   performance_condition,
   occurrence_count,
   last_occurrence_date,
   last_occurrence_time,
   last_response_date,
   last_response_time
FROM dbo.sysalerts
WHERE name=N'LAB_INT_ALERT_V2';
GO

SELECT TOP (30)
   j.name,
   h.step_id,
   h.step_name,
   h.run_status,
   h.run_date,
   h.run_time,
   h.run_duration,
   h.message
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j
 ON j.job_id=h.job_id
WHERE j.name=N'LAB_INT_ALERT_RESPONSE'
ORDER BY h.instance_id DESC;
GO

--Valide a evidência no database:
USE [<MEU_DATABASE>];
GO

SELECT *
FROM dbo.LAB_INT_Execucao
WHERE origem=N'ALERT_RESPONSE'
ORDER BY execucao_id DESC;
GO

--> Desabilite o Alert para ele não executar novamente. se for o caso
USE [msdb];
GO

EXEC dbo.usp_TenantSqlAgentAlert_Update
    @AlertName=N'LAB_INT_ALERT_V2',
    @Enabled=0;
GO

57. ROTACIONAR CREDENCIAL DO LINKED SERVER

/*
Antes deste bloco, altere a senha do <USUARIO_REMOTO> no SQL Server REMOTO para
<SENHA_REMOTA_NOVA>.
*/

--Depois, na instância SkyNova:
USE [master];
GO

-->
EXEC dbo.usp_TenantLinkedServer_SetCredential
   @ServerName=N'LAB_INT_LKS',
   @RemoteUser=N'<USUARIO_REMOTO>',
   @RemotePassword=N'<SENHA_REMOTA_NOVA>',
   @TestConnection=1;
GO

-->
EXEC dbo.usp_TenantLinkedServer_Test
   @ServerName=N'LAB_INT_LKS';
GO

-->
SELECT *
FROM OPENQUERY
(
   [LAB_INT_LKS],
   'SELECT
        SUSER_SNAME() AS RemoteLogin,
        DB_NAME()     AS RemoteDatabase,
        @@SERVERNAME  AS RemoteServer'
);
GO

--Valide novamente a procedure local que consome o Linked Server:
USE [<MEU_DATABASE>];
GO
EXEC dbo.LAB_INT_usp_ConsultarRemoto;
GO

58. EXECUTAR O JOB NOVAMENTE APÓS A ROTAÇÃO REMOTA

USE [msdb];
GO
EXEC dbo.sp_start_job
   @job_name=N'LAB_INT_JOB';
GO

--Aguarde a conclusão:
SELECT TOP (30)
   j.name,
   h.step_id,
   h.step_name,
   h.run_status,
   h.message,
   h.run_date,
   h.run_time
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j
 ON j.job_id=h.job_id
WHERE j.name=N'LAB_INT_JOB'
ORDER BY h.instance_id DESC;
GO

59. TROCAR A PRÓPRIA SENHA SQL — ROTAÇÃO REAL

ESTADO ANTES DA TROCA
------------------------------
SELECT
   ORIGINAL_LOGIN() AS OriginalLogin,
   SUSER_SNAME() AS ExecutionLogin,
   LOGINPROPERTY(ORIGINAL_LOGIN(),N'IsLocked') AS IsLocked,
   LOGINPROPERTY(ORIGINAL_LOGIN(),N'IsExpired') AS IsExpired;
GO

-->
EXEC master.dbo.usp_TenantLogin_MinhaSenha_Estado;
GO

60. TROCAR PARA SENHA TEMPORÁRIA

EXEC master.dbo.usp_TenantLogin_TrocarMinhaSenha
   @SenhaAtual=N'<SENHA_SQL_ATUAL>',
   @NovaSenha=N'<SENHA_SQL_TEMPORARIA>';
GO

/*
NOVA CONEXÃO REAL
------------------------------

Abra uma NOVA conexão no SSMS com:

    mesmo servidor/instância
    mesmo login
    senha = <SENHA_SQL_TEMPORARIA>

Na nova conexão execute:
*/

SELECT
    ORIGINAL_LOGIN() AS OriginalLogin,
    SUSER_SNAME() AS ExecutionLogin,
    SYSDATETIMEOFFSET() AS LoginTestTime;
GO

EXEC master.dbo.usp_TenantLogin_MinhaSenha_Estado;
GO

61. REVALIDAR INTEGRAÇÕES APÓS TROCA DA SENHA SQL

------------------------------
1 DATABASE LOCAL
------------------------------

USE [<MEU_DATABASE>];
GO
EXEC dbo.LAB_INT_usp_Cliente_Get;
GO

------------------------------
2 DATABASE MAIL
------------------------------

USE [msdb];
GO

EXEC dbo.usp_TenantDatabaseMail_Send
    @ProfileName=N'LAB_INT_MAIL_PROFILE',
    @recipients=N'<EMAIL_TESTE>',
    @subject=N'[SkyNova LAB] 05 - Após troca da senha SQL',
    @body=N'Nova sessão autenticada e Database Mail continua operacional.',
    @body_format=N'TEXT',
    @importance=N'NORMAL';
GO

------------------------------
3 LINKED SERVER
------------------------------

USE [master];
GO

EXEC dbo.usp_TenantLinkedServer_Test
    @ServerName=N'LAB_INT_LKS';
GO

------------------------------
4 SQL SERVER AGENT
------------------------------

USE [msdb];
GO

EXEC dbo.sp_start_job
    @job_name=N'LAB_INT_JOB';
GO

--Aguarde e consulte:

SELECT TOP (30)
    j.name,
    h.step_id,
    h.step_name,
    h.run_status,
    h.message,
    h.run_date,
    h.run_time
FROM dbo.sysjobhistory AS h
JOIN dbo.sysjobs AS j
  ON j.job_id=h.job_id
WHERE j.name=N'LAB_INT_JOB'
ORDER BY h.instance_id DESC;
GO

62. INVENTÁRIO FINAL ANTES DE APAGAR

------------------------------
1 DATABASE
------------------------------

USE [<MEU_DATABASE>];
GO

SELECT name,type_desc,create_date,modify_date
FROM sys.objects
WHERE name LIKE N'LAB_INT_%'
ORDER BY type_desc,name;

SELECT name,type_desc,is_disabled
FROM sys.indexes
WHERE object_id=OBJECT_ID(N'dbo.LAB_INT_Cliente');

SELECT name,is_table_type
FROM sys.types
WHERE name=N'LAB_INT_IdList';
GO

------------------------------
2 SQL AGENT
------------------------------

USE [msdb];
GO

SELECT name,enabled,SUSER_SNAME(owner_sid) AS OwnerLogin
FROM dbo.sysjobs
WHERE name IN(N'LAB_INT_JOB',N'LAB_INT_ALERT_RESPONSE');

SELECT name,enabled,email_address
FROM dbo.sysoperators
WHERE name=N'LAB_INT_OPERATOR';

SELECT name,enabled,performance_condition,occurrence_count
FROM dbo.sysalerts
WHERE name IN(N'LAB_INT_ALERT',N'LAB_INT_ALERT_V2');

SELECT category_id,name
FROM dbo.syscategories
WHERE category_class=1
  AND name IN(N'LAB_INT_CATEGORY',N'LAB_INT_CATEGORY_V2');
GO

------------------------------
3 DATABASE MAIL
------------------------------

EXEC dbo.usp_TenantDatabaseMail_List;
EXEC dbo.usp_TenantDatabaseMail_QueueStatus;
GO

------------------------------
4 LINKED SERVER
------------------------------

USE [master];
GO
EXEC dbo.usp_TenantLinkedServer_List;
GO

64. LIMPEZA FINAL — APAGAR TUDO DO AMBIENTE

--A ordem abaixo remove dependências de cima para baixo.

------------------------------
1 DESABILITAR ALERT
------------------------------

USE [msdb];
GO

IF EXISTS (SELECT 1 FROM dbo.sysalerts WHERE name=N'LAB_INT_ALERT_V2')
BEGIN
    EXEC dbo.usp_TenantSqlAgentAlert_Update
        @AlertName=N'LAB_INT_ALERT_V2',
        @Enabled=0;
END;
GO

------------------------------
2 REMOVER NOTIFICAÇÃO ALERT -> OPERATOR
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysalerts WHERE name=N'LAB_INT_ALERT_V2')
AND EXISTS (SELECT 1 FROM dbo.sysoperators WHERE name=N'LAB_INT_OPERATOR')
BEGIN
    EXEC dbo.usp_TenantSqlAgentAlert_SetNotification
        @AlertName=N'LAB_INT_ALERT_V2',
        @OperatorName=N'LAB_INT_OPERATOR',
        @NotificationMethod=1,
        @Remove=1;
END;
GO

------------------------------
3 EXCLUIR ALERT
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysalerts WHERE name=N'LAB_INT_ALERT_V2')
BEGIN
    EXEC dbo.usp_TenantSqlAgentAlert_Delete
        @AlertName=N'LAB_INT_ALERT_V2';
END;
GO

IF EXISTS (SELECT 1 FROM dbo.sysalerts WHERE name=N'LAB_INT_ALERT')
BEGIN
    EXEC dbo.usp_TenantSqlAgentAlert_Delete
        @AlertName=N'LAB_INT_ALERT';
END;
GO

------------------------------
4 DESLIGAR NOTIFICAÇÃO JOB -> OPERATOR
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name=N'LAB_INT_JOB')
AND EXISTS (SELECT 1 FROM dbo.sysoperators WHERE name=N'LAB_INT_OPERATOR')
BEGIN
    EXEC dbo.usp_TenantSqlAgentOperator_SetJobNotification
        @JobName=N'LAB_INT_JOB',
        @OperatorName=N'LAB_INT_OPERATOR',
        @NotifyLevelEmail=0;
END;
GO

------------------------------
5 EXCLUIR JOB PRINCIPAL
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name=N'LAB_INT_JOB')
BEGIN
    EXEC dbo.sp_delete_job
        @job_name=N'LAB_INT_JOB',
        @delete_unused_schedule=1;
END;
GO

------------------------------
6 EXCLUIR JOB DE RESPOSTA DO ALERT
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysjobs WHERE name=N'LAB_INT_ALERT_RESPONSE')
BEGIN
    EXEC dbo.sp_delete_job
        @job_name=N'LAB_INT_ALERT_RESPONSE',
        @delete_unused_schedule=1;
END;
GO

------------------------------
7 EXCLUIR OPERATOR
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysoperators WHERE name=N'LAB_INT_OPERATOR')
BEGIN
    EXEC dbo.usp_TenantSqlAgentOperator_Delete
        @OperatorName=N'LAB_INT_OPERATOR',
        @ConfirmOperatorName=N'LAB_INT_OPERATOR';
END;
GO

------------------------------
8 EXCLUIR CATEGORIA
------------------------------

IF EXISTS
(
    SELECT 1 FROM dbo.syscategories
    WHERE category_class=1 AND name=N'LAB_INT_CATEGORY_V2'
)
BEGIN
    EXEC dbo.usp_TenantSqlAgentCategory_Delete
        @CategoryName=N'LAB_INT_CATEGORY_V2',
        @CategoryClass=N'JOB';
END;
GO

IF EXISTS
(
    SELECT 1 FROM dbo.syscategories
    WHERE category_class=1 AND name=N'LAB_INT_CATEGORY'
)
BEGIN
    EXEC dbo.usp_TenantSqlAgentCategory_Delete
        @CategoryName=N'LAB_INT_CATEGORY',
        @CategoryClass=N'JOB';
END;
GO

------------------------------
9 EXCLUIR DATABASE MAIL ACCOUNT
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysmail_account WHERE name=N'LAB_INT_MAIL_ACCOUNT_AUX')
BEGIN
    EXEC dbo.usp_TenantDatabaseMail_DeleteAccount
        @AccountName=N'LAB_INT_MAIL_ACCOUNT_AUX',
        @ConfirmAccountName=N'LAB_INT_MAIL_ACCOUNT_AUX';
END;
GO

IF EXISTS (SELECT 1 FROM dbo.sysmail_account WHERE name=N'LAB_INT_MAIL_ACCOUNT')
BEGIN
    EXEC dbo.usp_TenantDatabaseMail_DeleteAccount
        @AccountName=N'LAB_INT_MAIL_ACCOUNT',
        @ConfirmAccountName=N'LAB_INT_MAIL_ACCOUNT';
END;
GO

------------------------------
10 EXCLUIR DATABASE MAIL PROFILE
------------------------------

IF EXISTS (SELECT 1 FROM dbo.sysmail_profile WHERE name=N'LAB_INT_MAIL_PROFILE')
BEGIN
    EXEC dbo.usp_TenantDatabaseMail_DeleteProfile
        @ProfileName=N'LAB_INT_MAIL_PROFILE',
        @ConfirmProfileName=N'LAB_INT_MAIL_PROFILE';
END;
GO

------------------------------
11 EXCLUIR OBJETOS DO DATABASE LOCAL
------------------------------

USE [<MEU_DATABASE>];
GO

IF OBJECT_ID(N'dbo.LAB_INT_Cli',N'SN') IS NOT NULL DROP SYNONYM dbo.LAB_INT_Cli;
IF OBJECT_ID(N'dbo.LAB_INT_vw_Ativos',N'V') IS NOT NULL DROP VIEW dbo.LAB_INT_vw_Ativos;
IF OBJECT_ID(N'dbo.LAB_INT_usp_ConsultarRemoto',N'P') IS NOT NULL DROP PROCEDURE dbo.LAB_INT_usp_ConsultarRemoto;
IF OBJECT_ID(N'dbo.LAB_INT_usp_Processar',N'P') IS NOT NULL DROP PROCEDURE dbo.LAB_INT_usp_Processar;
IF OBJECT_ID(N'dbo.LAB_INT_usp_Cliente_Get',N'P') IS NOT NULL DROP PROCEDURE dbo.LAB_INT_usp_Cliente_Get;
IF OBJECT_ID(N'dbo.LAB_INT_fn_Dobro',N'FN') IS NOT NULL DROP FUNCTION dbo.LAB_INT_fn_Dobro;
IF OBJECT_ID(N'dbo.LAB_INT_tr_Audit',N'TR') IS NOT NULL DROP TRIGGER dbo.LAB_INT_tr_Audit;
IF OBJECT_ID(N'dbo.LAB_INT_Remoto',N'U') IS NOT NULL DROP TABLE dbo.LAB_INT_Remoto;
IF OBJECT_ID(N'dbo.LAB_INT_Audit',N'U') IS NOT NULL DROP TABLE dbo.LAB_INT_Audit;
IF OBJECT_ID(N'dbo.LAB_INT_Execucao',N'U') IS NOT NULL DROP TABLE dbo.LAB_INT_Execucao;
IF OBJECT_ID(N'dbo.LAB_INT_Cliente',N'U') IS NOT NULL DROP TABLE dbo.LAB_INT_Cliente;
IF OBJECT_ID(N'dbo.LAB_INT_seq',N'SO') IS NOT NULL DROP SEQUENCE dbo.LAB_INT_seq;
IF TYPE_ID(N'dbo.LAB_INT_IdList') IS NOT NULL DROP TYPE dbo.LAB_INT_IdList;
GO

------------------------------
12 EXCLUIR LINKED SERVER
------------------------------

USE [master];
GO

IF EXISTS (SELECT 1 FROM sys.servers WHERE name=N'LAB_INT_LKS')
BEGIN
    EXEC dbo.usp_TenantLinkedServer_Drop
        @ServerName=N'LAB_INT_LKS',
        @ConfirmServerName=N'LAB_INT_LKS';
END;
GO

65. VALIDAÇÃO FINAL — ZERO RESÍDUOS DO AMBIENTE

------------------------------
1 DATABASE LOCAL
------------------------------

USE [<MEU_DATABASE>];
GO

SELECT name,type_desc
FROM sys.objects
WHERE name LIKE N'LAB_INT_%';

SELECT name,is_table_type
FROM sys.types
WHERE name=N'LAB_INT_IdList';
GO

RESULTADO ESPERADO:
    zero linhas.

------------------------------
2 SQL SERVER AGENT
------------------------------

USE [msdb];
GO

SELECT name
FROM dbo.sysjobs
WHERE name IN(N'LAB_INT_JOB',N'LAB_INT_ALERT_RESPONSE');

SELECT name
FROM dbo.sysoperators
WHERE name=N'LAB_INT_OPERATOR';

SELECT name
FROM dbo.sysalerts
WHERE name IN(N'LAB_INT_ALERT',N'LAB_INT_ALERT_V2');

SELECT name
FROM dbo.syscategories
WHERE name IN(N'LAB_INT_CATEGORY',N'LAB_INT_CATEGORY_V2');

SELECT name
FROM dbo.sysschedules
WHERE name IN(N'LAB_INT_DIARIO',N'LAB_INT_SEMANAL');
GO

RESULTADO ESPERADO:
    zero linhas em todas as consultas.

------------------------------
3 DATABASE MAIL
------------------------------

SELECT name
FROM dbo.sysmail_profile
WHERE name=N'LAB_INT_MAIL_PROFILE';

SELECT name
FROM dbo.sysmail_account
WHERE name IN(N'LAB_INT_MAIL_ACCOUNT',N'LAB_INT_MAIL_ACCOUNT_AUX');
GO

RESULTADO ESPERADO:
    zero linhas.

------------------------------
4 LINKED SERVER
------------------------------

USE [master];
GO

SELECT name
FROM sys.servers
WHERE name=N'LAB_INT_LKS';
GO

EXEC dbo.usp_TenantLinkedServer_List;
GO

RESULTADO ESPERADO:
    LAB_INT_LKS não existe mais.

------------------------------
5 LOGIN / SENHA
------------------------------

SELECT
    ORIGINAL_LOGIN() AS OriginalLogin,
    SUSER_SNAME() AS ExecutionLogin,
    LOGINPROPERTY(ORIGINAL_LOGIN(),N'IsLocked') AS IsLocked,
    LOGINPROPERTY(ORIGINAL_LOGIN(),N'IsExpired') AS IsExpired;
GO

EXEC master.dbo.usp_TenantLogin_MinhaSenha_Estado;
GO

/*
RESULTADO ESPERADO:
- login continua existindo;
- sessão autentica com <SENHA_SQL_FINAL>;
- nenhum login LAB foi criado pelo roteiro.
*/

Ajuda Skynova

Menu