Auditoria de DDL a nível banco e instância

Olá pessoal, vamos falar de auditoria DDL no SQL Server. A auditoria envolve a coleta e acompanhamento de eventos ocorridos em bases de dados. Nunca se sabe se um dia te pedirão a informação de quem apagou aquela tabela, stored procedure,banco de dados, etc. Vamos à solução:

  • Trigger DDL de banco de dados e Trigger de Instância.

Minha recomendação é que você crie no seu ambiente um banco de dados específico para auditoria. A preparação da auditoria de criação e alteração em objetos de banco de dados é realizada criando a trigger [tr_ddl_db] que irá capturar eventos realizados em stored procedures, functions, views, tabelas, índices, entre outros.

Passo a Passo:

  • Cria a tabela abaixo no seu banco de auditoria:

USE BD_AUDITORIA
GO
CREATE TABLE dbo.TD_AUDITORIA_DDL(
 IDAuditoriaDDL int IDENTITY(1,1) NOT NULL,
 TipoEvento varchar(30) NULL,
 Login varchar(100) NULL,
 Usuario varchar(100) NULL,
 Banco varchar(100) NULL,
 Comando varchar(max) NULL,
 DataEvento datetime NULL,
 Evento xml NULL,
 NivelAuditoria char(1) NOT NULL) 
--Cria a PK e o índice Clustered
ALTER TABLE dbo.TD_AUDITORIA_DDL ADD CONSTRAINT PK_AuditoriaDDL PRIMARY KEY CLUSTERED (IDAuditoriaDDL)
ALTER TABLE dbo.TD_AUDITORIA_DDL ADD DEFAULT (getdate()) FOR DataEvento
  • Cria a trigger DDL abaixo em todos os bancos de dados que serão auditados:

USE BancoAuditado 
GO
CREATE TRIGGER TR_DDL_DB ON DATABASE
 FOR DDL_DATABASE_LEVEL_EVENTS 
 AS 
 DECLARE @data xml 
 SET @data = eventdata() 
 INSERT INTO BD_AUDITORIA.dbo.TD_AUDITORIA_DDL
 (TipoEvento, Login, Usuario, Banco, Comando, Evento, NivelAuditoria )
 VALUES (data.value('(/EVENT_INSTANCE/EventType)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/LoginName)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/UserName)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/DatabaseName)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)1', 'nvarchar(2000)') 
 ,@data
 ,'D')

A preparação da auditoria de instância é realizada criando a trigger [tr_ddl_audit] que irá capturar eventos de instância como alteração/criação de logins e de banco de dados. A trigger deve ser criada uma única vez na instância.


CREATE TRIGGER [TR_DDL_AUDIT]
ON ALL SERVER 
FOR 
 ddl_endpoint_events, 
 ddl_login_events, 
 ddl_gdr_server_events, 
 ddl_authorization_server_events,
 create_database,
 alter_database,
 drop_database
AS 
 DECLARE @data xml 
 SET @data = eventdata() 
 INSERT INTO BD_AUDITORIA.dbo.TD_AUDITORIA_DDL
 (TipoEvento, Login, Usuario, Banco, Comando, Evento, NivelAuditoria) 
 VALUES (
 @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(2000)') 
 ,@data
 ,'S')

Após a criação dos objetos envolvidos se torna necessário ainda à execução do script abaixo para evitar erros de gravação em casos específicos.

USE BD_AUDITORIA
GO
EXECUTE sp_grantdbaccess guest
GO
GRANT INSERT ON dbo.TD_AUDITORIA_DDL TO public;

E pronto! A coleta de dados para auditoria será realizada de forma automática e você terá todas as alterações DDL armazenados na tabela BD_AUDITORIA.dbo.TD_AUDITORIA_DDL.

Até a próxima pessoal!!!

=)

 

Anúncios

Replicação Transacional – Erro 20598 – Linha não Encontrada no Assinante

Olá pessoal,

Vamos falar de um erro bem peculiar em uma replicação transacional.

Aparece o seguinte erro “20598” no replication monitor em uma assinatura com a seguinte descrição: “The row was not found at the Subscriber when applying the replicated command.

O erro acima diz que o registro não foi encontrado no assinante quando tentou aplicar um comando que veio do publicador. Este problema  gera uma fila de comandos de delete ou update, que ficarão aguardando com erro até que seja solucionado.

Breve Explicação

Quando se adiciona um novo assinante a uma publicação, a replicação cria basicamente três stored procedures de escrita no banco assinante para cada artigo do publicador:

  • sp_MSupd_NomeArtigo
  • sp_MSdel_NomeArtigo
  • sp_MSins_NomeArtigo

Um erro de um artigo numa replicação, implica no enfileiramento dos comandos de todos os artigos pertencentes a publicação.

Mão na Massa

Voltando ao erro 20598, vamos abordar a solução deste problema em dois cenários:

  • Erros de UPDATE (sp_MSupd_NomeArtigo)
  • Erros de DELETE (sp_MSdel_NomeArtigo)

Erros de UPDATE (sp_MSupd_NomeArtigo)

A correção de comandos de update do erro 20598 é mais complexa, pois será necessário inserir o registro no assinante para que a fila de comandos flua novamente. Serão necessários três passos para pegar o registro problemático:

1° passo (pegar o xact_seqno e command_id)

Segue abaixo a forma de obter o xact_seqno e command_id via replication monitor:

Erro_Replication_Monitor_01

Erro_Replication_Monitor_02

Segue abaixo a forma de obter o xact_seqno e command_id via t-sql no banco distribution:

Erro_Na_Tabela

use distribution
go
select top 1 xact_seqno,
command_id,
error_text,
time
from dbo.msrepl_errors
where error_code <> ''
and convert(varchar(1000),error_text) = 'the row was not found at the subscriber when applying the replicated command.'
order by time desc

2° passo (pegar o publisher_database_id)

SELECT DISTINCT subscriptions.publisher_database_id
FROM sys.servers AS [publishers]
INNER JOIN distribution.dbo.MSpublications AS [publications] ON publishers.server_id = publications.publisher_id
INNER JOIN distribution.dbo.MSarticles AS [articles] ON publications.publication_id = articles.publication_id
INNER JOIN distribution.dbo.MSsubscriptions AS [subscriptions] ON articles.article_id = subscriptions.article_id AND articles.publication_id = subscriptions.publication_id AND articles.publisher_db = subscriptions.publisher_db AND articles.publisher_id = subscriptions.publisher_id
INNER JOIN sys.servers AS [subscribers] ON subscriptions.subscriber_id = subscribers.server_id
WHERE
/*Instância Publicador*/publishers.name = 'NomeInstancia_Publicador'
/*Nome da Publicação*/ AND publications.publication = 'NomeDaPublicacao'
/*Instância Assinante*/AND subscribers.name = 'NomeInstancia_Assinante'

Database_id

3° passo (executar a sp_browsereplcmds com os parâmetros dos passos anteriores)

EXECUTE distribution.dbo.sp_browsereplcmds
@xact_seqno_start = '0x00007E600000035D0015',
@xact_seqno_end = '0x00007E600000035D0015',
@publisher_database_id = 2,
@command_id = 21

distribution

Após obter os comandos da procedure de update sp_MSupd_NomeArtigo”  faça os seguintes passos:

  •  Verifique a chave da tabela NomeArtigo.
  • Faça a consulta via t-sql no banco publicador com a chave dos dados obtidos na coluna command da procedure dbo.sp_browsereplcmds.
  • Pegue o registro obtido da consulta  e leve para o banco assinante de forma manual.

Erros de DELETE (sp_MSdel_NomeArtigo)

A correção de comandos de delete do erro 20598, será necessário alterar a procedure sp_MSdel_NomeArtigo com uma condição após o begin, para que seja feito um skip do erro:


USE [BancoAssinante]
GO
ALTER procedure [dbo].[sp_MSdel_NomeArtigo]
@pkc1 int,
@pkc2 smallint,
@pkc3 char(2)
as
begin

------------------------------------------------>>>>>>>>>>>>>>>>>>>>>
--Após o begin colocar a condição abaixo para resolver
--os comandos de DELETE:

if not exists (select * from [dbo].[NomeArtigo]
where [CHAVE_01] = @pkc1 and [CHAVE_02] = @pkc2 and [CHAVE_03] = @pkc3 )
return
------------------------------------------------<<<<<<<<<<<<<<<<<<<<<<

delete [dbo].[NomeArtigo]
where [CHAVE_01] = @pkc1
and [CHAVE_02] = @pkc2
and [CHAVE_03] = @pkc3
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end

OBS.: É de suma importância que se respeite a chave para não evitar problemas de integridade dos dados.

OBS2.: Para obter o nome da procedure sp_MSdel_NomeArtigo será necessário seguir os passos dos “Erros de UPDATE”, porém para a correção será necessário alterar o objeto conforme descrito acima. 

Pessoal esse é um assunto bastante complexo, espero que possa ter ajudado alguém de alguma forma.

Valeu!

God bless all!!!!

 

 

Permissão ao OpenRowSet para SQL Login

Olá pessoal!!!

Surgiu uma solicitação, onde um usuário pediu permissão para o comando OpenRowSet.

O usuário queria escrever em um excel através do banco de dados, mas teria que ser via t-sql.

O comando que o usuário queria rodar é o abaixo:


insert into OPENROWSET( 'Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\caminho\planilha.xlsx;', 'SELECT * FROM [Sheet1$]')

select * from Banco.dbo.Tabela

Primeiramente eu tive que instalar o provider “Microsoft.ACE.OLEDB.12.0”, que se encontra no link: “http://www.microsoft.com/en-us/download/details.aspx?id=13255&#8221;.

Após a instação do provider “Microsoft.ACE.OLEDB.12.0”, veio um dilema, qual a permissão seria dada para o usuário SQL Login para o comando OpenRowSet?

Pesquisei na documentação da microsoft “https://technet.microsoft.com/pt-br/library/ms175915(v=sql.105).aspx” e identifiquei que seria necessário a concessão de permissão da serverrole: bulkadmin.

Mesmo após a permissão acima, o usuário apresentou o seguinte erro: Ad hoc access to OLE DB provider ‘Microsoft.ACE.OLEDB.12.0’ has been denied.

Identifiquei no link “http://dba.stackexchange.com/questions/61739/help-with-sql-server-error-ad-hoc-access-to-ole-db-provider-microsoft-ace-oled&#8221;, que seria necessário registrar uma chave no Registry Editor, ou o regedit.

Os passos que segui foram os seguintes:

Passo 01: Escrevi regedit

Passo 02: Dentro do “Registry Editor”, fui em HKEY_LOCAL_MACHINE

Passo 03: Fui em SOFTWARE

Passo 04: Fui em Microsoft

Passo 05: Fui em Microsoft Sql Server

Passo 06: Fui em MSSQL10_50.MSSQLSERVER

Passo 07: Fui em Providers

Passo 08: Fui em Microsoft.ACE.OLEDB.12.0

Após chegar na pasta “Microsoft.ACE.OLEDB.12.0”, adicionei a chave “DWORD (32-bit) Value” com o nome “DisallowAdhocAccess” e com o valor hexadecimal 0 (zero).

Excel

Pronto! após a permissão de bulkadmin e a adição da chave no Registry Editor o usuário teve permissão no comando OpenRowSet.

OBS.: É importante que a conta de serviço do SQL tenha permissão na pasta de origem, onde será feito a leitura e/ou escrita do excel.

Descriptografando Procedures e Functions

Bom dia Pessoal!

Então venho abordar sobre um assunto um pouco polêmico da área de encriptação de objetos do SQL Server.

Até onde eu sabia, não era possível descriptografar procedures ou functions declarados com a cláusula WITH ENCRYPTION.

Ai que eu estava enganado. Existe sim uma solução, mas para que ela funcione é necessário que você conecte-se no SSMS via DAC (Dedicated Administrator Connection).

Opa, mas o que é o DAC?

Breve resumo do DAC

O DAC é uma conexão com o propósito de diagnósticos e resolução de problemas no SQL Server em casos onde a instância esteja em uma situação extrema e rara.

Conforme a documentação http://msdn.microsoft.com/pt-br/library/ms189595.aspx: “O SQL Server esforça-se o máximo para o êxito de uma conexão DAC, mas pode falhar em situações extremas”.

Ou seja o SQL Server guarda recursos para a conexão DAC.

Um exemplo prático é através da DMV (Dynamic Management View)  sys.dm_os_schedulers:

select * from sys.dm_os_schedulers where status = 'VISIBLE ONLINE (DAC)'

É de suma importância que em sua instância esteja habilitado a opção a nível server “Remote Admin Connections”:

--Habilita o DAC (Remote admin connections)
EXEC sp_configure 'remote admin connections', 1
GO
RECONFIGURE

Para se conectar ao DAC é necessário logar com o nome admin:NomeServidor.

Lembrando que não há suporte para logar no DAC através do Object Explorer. Para logar é necessário abrir uma New Query no SSMS, sem o Object Explorer. Se preferir você pode se conectar via CMD através do comando sqlcmd -S NomeServidor -U sa -P Senha -A. A conta que você se conectar deve ser obrigatoriamente SYSADMIN.

Para identificar uma conexão DAC é através do enpoint_id = 1 da sys.dm_exec_sessions ou através da porta 1434 na sys.dm_exec_connections. Só é possível conectar uma sessão via DAC.

admin

Endpoint

 Voltando a solução de Descriptografia

Após estar conectado via DAC você poderá fazer a consulta na System Table sys.sysobjvalues coluna varbinary imageval. Quando você não está conectado via DAC, não é possível consultar algumas system tables. Está system table funciona no contexto de banco, igual a sys.procedures.

Passo a Passo

1. Crie a procedure model.dbo.SP_TESTE:

use model
GO
CREATE PROCEDURE dbo.SP_TESTE
WITH ENCRYPTION
AS
SELECT 1,2,3

 2. Faça as consultas abaixo via DAC:

objvalues


USE model
GO
SELECT object_name(objid) as NomeObjeto,objid as "Object_Id", ImageVal, 'sys.sysobjvalues' as TableSystem FROM sys.sysobjvalues
WHERE object_name(objid) = 'SP_TESTE'

SELECT object_name(Object_Id) as NomeObjeto,Object_Id, definition, 'sys.sql_modules' as TableSystem FROM sys.sql_modules
WHERE object_name(Object_Id) = 'SP_TESTE'

SELECT routine_name as NomeObjeto,2105058535 as "Object_Id" , routine_definition, 'information_schema.routines' as TableSystem FROM information_schema.routines
WHERE routine_name = 'SP_TESTE'

 

Obs.: Repare que a sys.sql_modules e information_schema.routines retornam NULL do código encriptado. Somente a sys.sysobjvalues retorna o varbinary.

3. Crie a procedure master.dbo.SP_Descriptografa_Objeto, que fará descriptografia do objeto SP_TESTE, banco model.

USE master
GO
CREATE PROC dbo.SP_Descriptografa_Objeto

/*******************************************************************************************************
 Nome: dbo.SP_Descriptografa_Objeto
 Adaptações: Patrocinio Maia
 Link Referência: http://jongurgul.com/blog/sql-object-decryption/
 DESCRIÇÃO: Faz a Descriptografia de qualquer objeto no Banco de Dados. Para rodá-la é necessário estar conectado via DAC (Dedicated Administrator Connection) "admin:NomeServer".
 Basicamente esta procedure faz um SELECT na "sys.sysobjvalues", coluna imageval do banco e objeto especificado.
 MODO DE USO: EXEC master.dbo.SP_Descriptografa_Objeto 'NomeBanco', 'NomeObjeto'
 Compilação...: SQL SERVER 2008 R2
 ******************************************************************************************************/ 

@NomeBanco varchar(300),@NomeObjeto varchar(300)

WITH ENCRYPTION

AS

DECLARE @ImprimeCodigoEncriptado NVARCHAR(MAX) =

N'
DECLARE @EncObj VARBINARY(MAX)
 ,@DummyEncObj VARBINARY(MAX)
 ,@ObjectNameStmTemplate NVARCHAR(MAX)

SET NOCOUNT ON

 USE '+@NomeBanco+'

DECLARE @object_id INT
 ,@name SYSNAME

SELECT @object_id = [object_id]
 ,@name = [name]
FROM sys.all_objects
WHERE NAME = N'''+@NomeObjeto+'''

SELECT TOP 1 @ObjectNameStmTemplate = [ObjectStmTemplate]
 ,@EncObj = [imageval]
FROM (
 SELECT SPACE(1) + (
 CASE
 WHEN [type] = ''P''
 THEN N''PROCEDURE''
 WHEN [type] = ''V''
 THEN ''VIEW''
 WHEN [type] IN (
 ''FN''
 ,''TF''
 ,''IF''
 )
 THEN N''FUNCTION''
 WHEN [type] IN (''TR'')
 THEN N''TRIGGER''
 ELSE [type]
 END
 ) + SPACE(1) + QUOTENAME(SCHEMA_NAME([schema_id])) + ''.'' + QUOTENAME(ao.[name]) + SPACE(1) + (
 CASE
 WHEN [type] = ''P''
 THEN N''WITH ENCRYPTION AS''
 WHEN [type] = ''V''
 THEN N''WITH ENCRYPTION AS SELECT 123 ABC''
 WHEN [type] IN (''FN'')
 THEN N''() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1 END''
 WHEN [type] IN (''TF'')
 THEN N''() RETURNS @t TABLE(i INT) WITH ENCRYPTION AS BEGIN RETURN END''
 WHEN [type] IN (''IF'')
 THEN N''() RETURNS TABLE WITH ENCRYPTION AS RETURN SELECT 1 N''
 WHEN [type] IN (''TR'')
 THEN N'' ON '' + OBJECT_NAME(ao.[parent_object_id]) + '' WITH ENCRYPTION FOR DELETE AS SELECT 1 N''
 ELSE [type]
 END
 ) + REPLICATE(CAST(N''-'' AS NVARCHAR(MAX)), DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
 ,sov.[imageval]
 FROM sys.all_objects ao
 INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1
 AND ao.[Object_id] = sov.[objid]
 WHERE [type] NOT IN (
 ''S''
 ,''U''
 ,''PK''
 ,''F''
 ,''D''
 ,''SQ''
 ,''IT''
 ,''X''
 ,''PC''
 ,''FS''
 ,''AF''
 ,''TR''
 )
 AND ao.[object_id] = @object_id

 UNION ALL

 --Server Triggers

 SELECT SPACE(1) + ''TRIGGER'' + SPACE(1) + QUOTENAME(st.[name]) + SPACE(1) + N''ON ALL SERVER WITH ENCRYPTION FOR DDL_LOGIN_EVENTS AS SELECT 1'' + REPLICATE(CAST(N''-'' AS NVARCHAR(MAX)), DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
 ,sov.[imageval]
 FROM sys.server_triggers st
 INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1
 AND st.[object_id] = sov.[objid]
 WHERE st.[object_id] = @object_id
 --Database Triggers

 UNION ALL

 SELECT SPACE(1) + ''TRIGGER'' + SPACE(1) + QUOTENAME(dt.[name]) + SPACE(1) + N''ON DATABASE WITH ENCRYPTION FOR CREATE_TABLE AS SELECT 1'' + REPLICATE(CAST(N''-'' AS NVARCHAR(MAX)), DATALENGTH(sov.[imageval])) COLLATE DATABASE_DEFAULT
 ,sov.[imageval]
 FROM sys.triggers dt
 INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1
 AND dt.[object_id] = sov.[objid]
 AND dt.[parent_class_desc] = ''DATABASE''
 WHERE dt.[object_id] = @object_id
 ) x([ObjectStmTemplate], [imageval])

--Altera o objeto existente, em seguida, reverte para que possamos ter o valor do objeto criptografado dummy
BEGIN TRANSACTION

DECLARE @sql NVARCHAR(MAX)

SET @sql = N''ALTER'' + @ObjectNameStmTemplate

EXEC sp_executesql @sql

SELECT @DummyEncObj = sov.[imageval]
FROM sys.all_objects ao
INNER JOIN sys.sysobjvalues sov ON sov.[valclass] = 1
 AND ao.[Object_id] = sov.[objid]
WHERE ao.[object_id] = @object_id

ROLLBACK TRANSACTION

DECLARE @Final NVARCHAR(MAX)

SET @Final = N''''

DECLARE @Pos INT

SET @Pos = 1

WHILE @Pos <= DATALENGTH(@EncObj) / 2
BEGIN
 SET @Final = @Final + NCHAR(UNICODE(SUBSTRING(CAST(@EncObj AS NVARCHAR(MAX)), @Pos, 1)) ^ (UNICODE(SUBSTRING(N''CREATE'' + @ObjectNameStmTemplate, @Pos, 1)) ^ UNICODE(SUBSTRING(CAST(@DummyEncObj AS NVARCHAR(MAX)), @Pos, 1))))
 SET @Pos = @Pos + 1
END

IF DATALENGTH(@Final) <= 8000
BEGIN
 PRINT @Final
END
ELSE
BEGIN
 DECLARE @c INT

 SET @c = 0

 WHILE @c <= (DATALENGTH(@Final) / 8000)
 BEGIN
 PRINT SUBSTRING(@Final, 1 + (@c * 4000), 4000)

 SET @c = @c + 1
 END
END'

--Faz a mágica
EXEC(@ImprimeCodigoEncriptado)

4. Execute a procedure  master.dbo.SP_Descriptografa_Objeto com os parâmetros ‘model’, ‘SP_TESTE’:

sp_descriptografa_objeto

Pronto! Habemus o Código!

A procedure master.dbo.SP_Descriptografa_Objeto é baseada no varbinary  (imageval) retornado  da System Table sys.sysobjvalues. Ela basicamente faz a conversão do varbinary em uma String (Varchar).

Galera, desvendado o mistério, agora os DBAs poderão abrir os códigos encriptados, Heheheheheheheh!!!!

Jesus os abençoe!!! =)

 

Trigger DDL Logon – Bloqueando acesso de SQL Logins através do SSMS

Bom dia pessoal,

Vamos falar de um assunto um tanto corriqueiro nas instituições hoje. Sabe aquele problema: “Poxa! O cara do desenvolvimento logou com o usuário da aplicação (SQL Login) no SQL Server Management Studio (SSMS), apagou registros de uma tabela de produção importantíssima e tive subir um point in time de uma base de 100 GB, que gerou aquele estresse!”.                                                                                                                                                     Uma solução simples seria a mudança da senha de tal usuário:

ALTER LOGIN NomeLogin WITH PASSWORD = 'SenhaForte', CHECK_POLICY =OFF, CHECK_EXPIRATION = OFF

Mas como existem várias aplicações em suas “string connections” utilizando aquela senha, seria uma tarefa um tanto inviável.                                                                                                                                                                                                                                                                             O que fazer então?  Vou ficar a míngua do desenvolvedor? Oh! E agora quem poderá me defender?

Eu! O SQL Colorado!!!!!!!!! O desenvolvedor, não contava com a minha astúcia!!!!! kkkkkkkkkkkkkkkkkkkkkkkk

CHAUP

Deixando de lado as brincadeiras vamos a solução: Trigger DDL!                                                                                                                                                                                                                       Passo a passo!

01. Crie a tabela dbo.TBAuditoriaUsuarios:

USE master /*Ou o banco administrativo que desejar*/
GO
CREATE TABLE [dbo].[TBAuditoriaUsuarios](
 [IDAuditoriaUsuarios] [int] IDENTITY(1,1) NOT NULL,
 [SPID] [int] NULL,
 [DataEvento] [smalldatetime] NULL,
 [NomeHost] [varchar](300) NULL,
 [NomePrograma] [varchar](300) NULL,
 [NomeLogin] [varchar](50) NULL,
 CONSTRAINT [PK_IDAuditoriaUsuarios] PRIMARY KEY CLUSTERED
([IDAuditoriaUsuarios] ASC)ON [PRIMARY])

02. Crie a Trigger DDL -> TR_USERS_AUDIT :

CREATE TRIGGER [TR_USERS_AUDIT]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

/*No ORIGINAL_LOGIN você vai definir a nomenclatura padrão dos "Logins" do seu ambiente*/
IF (ORIGINAL_LOGIN() LIKE 'User%') AND APP_NAME() LIKE 'Microsoft SQL Server Management Studio%' 

 BEGIN

 ROLLBACK;

 /*Coleta na tabela dbo.TBAuditoriaUsuarios a tentativa de acesso*/
 INSERT INTO master.dbo.TBAuditoriaUsuarios
 SELECT @@SPID,GETDATE(),HOST_NAME(),APP_NAME(),ORIGINAL_LOGIN()

 /*Mensagem de Erro que aparecerá na sp_readerrorlog*/ 

 PRINT 'A Trigger DDL "TR_USERS_AUDIT" bloqueou o acesso do HostName '+HOST_NAME()+ ' logando com o Login '+ORIGINAL_LOGIN()

 END

END

/*
ENABLE TRIGGER [TR_USERS_AUDIT] ON ALL SERVER
DISABLE TRIGGER [TR_USERS_AUDIT] ON ALL SERVER
*/

All right! Agora faça os testes em seu ambiente:

CREATE LOGIN UserTeste WITH PASSWORD = '123', CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Agora aparecerá a seguinte mensagem de erro no SSMS:

Erro Acesso

Confira o acesso indevido na tabela de auditoria dbo.TBAuditoriaUsuarios.

Apague o usuário de teste UserTeste:


DROP LOGIN UserTeste;

No exemplo acima a Trigger DDL Logon bloqueou o acesso de usuários que começam com ‘User%’ no programa ‘Microsoft SQL Server Management Studio%’.

Pessoal, basicamente é isso!!! Agradeço a todos!!

Jesus bless everybody!!!!

Verificando o Buffer Cache do SQL Server – dm_os_buffer_descriptors

Olá pessoal!

Após alguns meses sem postar, venho aqui falar sobre um assunto muito importante para o SQL Server: “O Buffer Cache”.

O Buffer Cache é a memória que o SQL Server utiliza para executar planos de execução, alocar páginas e rodar as mais variadas querys em uma instância.

gerenciador do Buffer

A consulta em memória é muito mais rápida, que uma consulta em disco (paginação). Para limpeza do Buffer Cache primeiramente rode o comando:

--Persiste as páginas de alteração no disco
CHECKPOINT

Depois rode o comando:

--Remove todas as páginas existentes no Buffer Cache
DBCC DROPCLEANBUFFERS

OBS.: A operação acima não é recomendada para ambientes críticos.

Conforme a technet: “http://technet.microsoft.com/pt-br/library/aa337525(v=sql.105).aspx”, um buffer é uma página de 8KB de memória, ou seja, o buffer cache é dividido em várias páginas de 8KB.

Conforme as consultas vão ocorrendo o Gerenciador do Buffer Cache vai alocando novas páginas e substituindo as páginas menos utilizadas e ou mais antigas, para atender as demandas do banco. Esse processo de “Substituição/Novas Páginas” é conhecido como Clock Hands (Ponteiro de Relógio). É possível pesquisar o Clock Hands através da Dynamic View “sys.dm_os_memory_cache_clock_hands “. Para nos ajudar a verificar a volumetria do Buffer Cache, existe uma Dynamic View : sys.dm_os_buffer_descriptors, que lista todas páginas alocadas no Buffer Cache por Banco de Dados.

Fiz uma procedure que traz o tamanho total do buffer da instância em relação ao Banco de dados e a segunda opção que detalha o tamanho alocado por objeto de banco.


USE master
GO
/*
/************Modelos de Execução**************/
--Retorna os Databases que estão usando o Buffer Cache
EXEC master.dbo.SP_VerificaBufferCache --&amp;quot;Sem Parâmetro&amp;quot; 
--Retorna os Objetos do Banco de Dados Especificado que estão usando o Buffer Cache 
EXEC master.dbo.SP_VerificaBufferCache 'NomeBanco' 
*/
CREATE PROCEDURE [dbo].[SP_VerificaBufferCache]

@NomeBanco varchar(300) = NULL

AS
SET NOCOUNT ON

IF OBJECT_ID ('tempdb..#dm_os_buffer_descriptors') IS NOT NULL DROP TABLE #dm_os_buffer_descriptors
IF OBJECT_ID ('tempdb..#QTDPaginasBufferCachePorBanco') IS NOT NULL DROP TABLE #QTDPaginasBufferCachePorBanco

CREATE TABLE #QTDPaginasBufferCachePorBanco
(NomeBanco VARCHAR(300),NomeObjeto VARCHAR(500),QTDPaginasEmCacheObjeto INT,ComandoSPSpaceUsed NVARCHAR(1000))

--Carrega a tabela temporária #dm_os_buffer_descriptors
SELECT *
INTO #dm_os_buffer_descriptors
FROM sys.dm_os_buffer_descriptors

IF @NomeBanco IS NULL

BEGIN
--Top Databases pela Qtd de páginas no buffer cache.
SELECT NomeBanco,
QTDPaginasEmCacheDB,
CASE WHEN (QTDPaginasEmCacheDB * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),(QTDPaginasEmCacheDB * 8)) + ' KB'
WHEN (QTDPaginasEmCacheDB * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024/1024)) + ' GB'
END AS TamanhoBufferCacheDB,
CONVERT(DECIMAL(10,4),CONVERT(decimal(10,2),QTDPaginasEmCacheDB) * 100.00 /(SELECT CONVERT(decimal(10,2),COUNT(*)) FROM #dm_os_buffer_descriptors)) AS [PorcentagemUso],
CASE WHEN ((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)) + ' KB'
WHEN ((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),(((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),(((SELECT COUNT(*) FROM #dm_os_buffer_descriptors) * 8)/1024/1024)) + ' GB'
END AS TamanhoTotalBufferCache

FROM (
SELECT DB_NAME(database_id) as NomeBanco,
COUNT(*)AS QTDPaginasEmCacheDB
FROM #dm_os_buffer_descriptors
GROUP BY DB_NAME(database_id) ) AS Top_DB

ORDER BY QTDPaginasEmCacheDB DESC;
END

IF @NomeBanco IS NOT NULL

BEGIN

DECLARE @SQL nvarchar(MAX) =
--Qtd Páginas por Objeto e Banco
N'USE '+@NomeBanco+'

INSERT INTO #QTDPaginasBufferCachePorBanco
SELECT
db_name(database_id) as NomeBanco,
name as NomeObjeto,
COUNT(*) AS QTDPaginasEmCacheObjeto,
''EXEC '+@NomeBanco+'.dbo.sp_spaceused ''+name+'' '' As ComandoSPSpaceUsed
FROM #dm_os_buffer_descriptors AS bd
INNER JOIN (SELECT object_name(object_id) AS name,
index_id,
allocation_unit_id
FROM '+@NomeBanco+'.sys.allocation_units AS au
INNER JOIN '+@NomeBanco+'.sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)
UNION ALL

SELECT
object_name(object_id) AS name,
index_id,
allocation_unit_id
FROM '+@NomeBanco+'.sys.allocation_units AS au
INNER JOIN '+@NomeBanco+'.sys.partitions AS p ON au.container_id = p.partition_id AND au.type = 2 ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = DB_ID('''+@NomeBanco+''')
GROUP BY name, db_name(database_id)
ORDER BY QTDPaginasEmCacheObjeto DESC'

--Insere na tabela temporária #QTDPaginasBufferCachePorBanco
EXEC (@SQL)

--Qtd de Páginas no Buffer Cache por Objeto de um Banco
SELECT t1.NomeBanco,
t1.NomeObjeto,
QTDPaginasEmCacheObjeto,
CASE WHEN (QTDPaginasEmCacheObjeto * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),(QTDPaginasEmCacheObjeto * 8)) + ' KB'
WHEN (QTDPaginasEmCacheObjeto * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),((QTDPaginasEmCacheObjeto * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),((QTDPaginasEmCacheObjeto * 8)/1024/1024)) + ' GB'
END AS TamanhoBufferCacheObjeto,
CASE WHEN (QTDPaginasEmCacheDB * 8)/1024 = 0 THEN CONVERT(VARCHAR(30),(QTDPaginasEmCacheDB * 8)) + ' KB'
WHEN (QTDPaginasEmCacheDB * 8)/1024/1024 = 0 THEN CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024)) + ' MB'
ELSE CONVERT(VARCHAR(30),((QTDPaginasEmCacheDB * 8)/1024/1024)) + ' GB'
END AS TamanhoBufferCacheDB,
convert(decimal(10,4),convert(decimal(10,2),QTDPaginasEmCacheObjeto) * 100.00 / CONVERT(decimal(10,2),QTDPaginasEmCacheDB)) AS [PorcentagemUso] ,
t1.ComandoSPSpaceUsed

FROM #QTDPaginasBufferCachePorBanco t1
inner join (SELECT DB_NAME(database_id) as NomeBanco,
COUNT(*)AS QTDPaginasEmCacheDB
FROM #dm_os_buffer_descriptors
--Aqui é filtrado o banco da tabela temporária #dm_os_buffer_descriptors
WHERE DB_NAME(database_id) = @NomeBanco
GROUP BY DB_NAME(database_id)) t2 on t1.NomeBanco = t2.NomeBanco

ORDER BY QTDPaginasEmCacheObjeto DESC

END

Espero que tenha ajudado pessoal!

Cristo os abençoe!!!

SSIS 2012 – Usando Variáveis como saída e entrada – (Result Set)

Oi pessoal!

Então tive uma demanda no trabalho para consumo de um web service. A demanda foi um pouco difícil, por tanto, vou tentar ser o mais simples possível.

Breve História

O pessoal da parte de programação fez um código C# em uma Script Task para consumir os dados do Web Service. O Script Task se comunicava com o WS, porém não tinhamos uma forma de retornar os dados como uma saída. Surgiu a idéia de gerar um arquivo xml, a partir de variáveis do Integration, sendo colocado dentro do código C#. Foi criado várias variáveis. Como na figura abaixo.

variable

No Script Task configurei na parte ReadOnlyVariables e ReadWriteVariables, as variáveis que seriam utilizadas dentro do código C#, de forma dinâmica, sem a necessidade de alteração no código.

Scrip task

No código C#, depois de pesquisar muito, coloquei as variáveis como entrada e saída dentro do código.

Scrip código

Maravilha! Agora eu conseguia importar os dados na data e o ID que eu desejava através de variáveis.

Usando Variáveis com  Execute SQL Task (Parameter Mapping) –Inserção em uma tabela

Inserindo dados de uma variável em uma tabela (Passo a passo):

Passo 01 (Arraste a task e configure a conexão)

passo 01

Passo 02 (Clique em Parameter Mapping, clique em Add e adicione as variáveis que deseja. Sete para varchar o tipo e em Parameter Name coloque a ordem que as variáveis terão dentro do código T-SQL). No exemplo abaixo o código receberá primeiro a vAno e em seguida a vMes.

passo 02


DECLARE @tempo_mes table (mes char(02))
DECLARE @tempo_ano table (ano char(04))
DECLARE @mes char(02),@ano char(04)

/*Pega a variável  [User::vAno] - Parameter Name = 0,
o ponto de interrogação corresponde a variável dentro do código t-sql*/

insert into @tempo_ano (ano) values (?)

/*Pega a variável [User::vMes] - Parameter Name = 1,
o ponto de interrogação corresponde a variável dentro do código t-sql*/

insert into @tempo_mes (mes) values (?)

SET @mes = (SELECT * FROM @tempo_mes )
SET @ano = (SELECT * FROM @tempo_ano )

INSERT INTO DB_ONS_WS.dbo.TAB_TEMPO

SELECT @MES AS MES, @ANO AS ANO

 

Passo 03 (Clique em SQL Statement e Insira o código T-SQL acima e clique em OK)
passo 03

Passo 04 (Execute a task e selecione a tabela).

Usando Variáveis com  Execute SQL Task (Result Set) – Inserção de um dado de tabela em uma variável

Inserindo dados de uma tabela para uma variável (Passo a passo):

Passo 01 (Arraste a task, configure a conexão e em Result Set coloque a opção Single Row)

Passo 02 (Clique em Result Set, clique em Add, em Variable Name escolha a variável que você deseja que seja inserido o dado da tabela e em Result Name coloque o Alias (apelido) ou o nome da coluna da consulta desejada). No exemplo abaixo,a variável vAno receberá o max(ano) da tabela DB_ONS_WS.dbo.TAB_TEMPO.

Observação: É muito importante que o Result Name esteja igual ao Alias ou coluna da tabela.

passo 054

Passo 03 (Clique em SQL Statement e coloque o código abaixo).


Use DB_ONS_WS

--O Alias Ano é o meu Result Name

select max(ano) as Ano from dbo.TAB_TEMPO

Passo 06

Passo 04 (Arraste uma segunda task e Clique com o botão direito na Execute SQL Task e clique em Edit Breakpoints… e selecione a primeira opção. Depois execute o pacote, vá em Locals-> Variables e procure o resultado da variável).

Passo 07

PRINT 08

Então pessoal!

Foi um assunto um pouco longo, porém mão-na-roda. As variables facilitam e muito, as  manutenções no pacote que vierem a surgir.

Espero ter ajudado de alguma forma, pois foi muito útil para mim!

Abraços,

E que Jesus os abençoe muito!!!

Entendendo e Resolvendo problemas com Usuários Órfãos (Orphaned User)

Bom dia galera!

Vamos falar um pouco desse assunto que é muito importante e deve ser averiguado depois de um processo de restauração de banco.

Você já deve ter se perguntado, por quê um usuário de um banco de dados fica “Órfão”?

orfao_

Antes de responder a pergunta acima, é importante sabermos sobre o Identificador de Segurança do SQL Server conhecido como SID (Security Identifier). Em poucas palavras, o SID é a ligação do Login a um Usuário de banco. Para consulta dos logins e usuários existem duas views do sistema ->


select name AS LOGIN, sid AS "SID" from bancodedados.sys.syslogins

select name AS USUARIO, sid AS "SID" from bancodedados.sys.sysusers

Depois dessa breve explicação sobre o SID, vamos a resposta da pergunta sobre a ocorrência de um usuário órfão. Segundo a technet -> http://technet.microsoft.com/pt-br/library/ms175475.aspx, a causa para um usuário se tornar órfão depois de um processo de restauração, é por quê o mesmo é mapeado para uma SID que não está presente na nova instância restaurada, ou seja esse usuário fica sem um login, pois perdeu a ligação com a SID.

Depois da explicação sobre como um usuário se torna Órfão, vamos agora a solução de tal problema.Rode a procedure abaixo para verificar todos os usuários que ficaram órfãos.


use AdventureWorks2012

go

EXEC sp_change_users_login 'Report'

sp_change_users_login-report

Depois execute o código abaixo que atribuirá o usuário db_login1 ao login com o mesmo nome.


Use AdventureWorks2012
GO
EXEC sp_change_users_login 'Auto_Fix','db_login1'

Na prática o sql server está fazendo o seguinte procedimento:


ALTER USER db_login1 with login = dblogin1

E depois atribui um SID válido para tal usuário.

Mas o parâmetro ‘Auto_Fix ‘ só altera o usuário, quando o nome do mesmo é igual ao nome do login. Para nomes diferentes (usuário/login) é só executar a procedure sp_change_users_login com os seguintes parâmetros.


Use AdventureWorks2012

GO

EXEC sp_change_users_login 'Update_One','db_login1','Outro_Login'

Concluindo

É importantíssimo corrigir os usuários órfãos, pois as aplicações de uma corporação necessitam de que seus usuários de banco estejam devidamente mapeados aos seus logins.

Valeu pessoal!

Executando Scripts .sql com xp_cmdshell

Boa tarde galera!

Então estou fazendo meu primeiro post sobre assuntos de SQL Server.

Let’s GO!        openterm_png_optimized

Vamos falar sobre o comando xp_cmdshell  e tendo como exemplo a execução de códigos t-sql através de um script .sql.

Em resumo o  xp_cmdshell executa comandos shell do windows.

Eu sempre tive  dúvida se era possível executar scripts t-sql de um diretório através do SSMS (Sql Server Management Studio).No prompt de comando existe a opção “sqlcmd” que te dá a possibilidade de realizar a conexão com o SQL Server e fazer consultas, inserções e até a execução de scripts .sql.

Vamos ao exemplo, usando o comando xp_cmdshell com o sqlcmd do DOS, mas executando é claro no Management Studio…

Crie uma pasta no C:\Script_sql\.

paint 1

Para que o SQL Server reconheça a pasta, a conta de serviço da engine precisa ter permissão.

OBS.: Caso você não saiba qual conta está configurada para o serviço do database engine. Execute o seguinte código no SSMS:

declare @sqlserver varchar(200)

EXEC master..xp_instance_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SYSTEM\CurrentControlSet\Services\MSSQLSERVER',

@value_name='objectname', @value= @sqlserver OUTPUT

print 'A Conta de Serviço da Engine do SQL Server é:
' +convert(varchar(30),@sqlserver)

Continuando a configuração da pasta Script_sql, você clica com o botão direito, depois em Propriedades-> guia Segurança-> Editar -> Adicionar. Digita a conta do sql server e clica em verificar nomes e depois dá um OK.

OBS.: Para executar essas ações você precisa ser administrador ou proprietário da pasta.

Depois clica em controle total para a conta do SQL Server.

O Script “instpubs”que está na pasta “Scripts_sql”, cria o banco de dados pubs, objetos e os dados do mesmo.

Dada as devidas permissões  para a pasta vamos executar o código t-sql através do SSMS.Entre no Management Studio e digite o código que segue abaixo:


/*Declaração das variáveis do comando xp_cmdshell*/

Declare @comando varchar(800),
        @arquivo varchar(100),
        @servidor varchar(50),
        @caminho varchar(100),
        @extensao varchar(10)

set @servidor = N'(local)' /*Nome do Servidor Local*/
set @caminho = N'C:\Scripts_SQL\' /*Caminho Raíz*/
set @extensao = N'.sql' /*Extensao do Arquivo*/
set @arquivo = N'instpubs' /*Arquivo .sql que está na pasta*/

--Comando que será executa pelo xp_cmdshell
set @comando = N'sqlcmd -S ' + @servidor + ' -i ' + @caminho+
@arquivo + @extensao

exec master.dbo.xp_cmdshell @comando

O comando acima produzirá o seguinte resultado no SQL Server.

paint 5

Resumindo, o comando xp_cmdshell fez com que o SQL Server Management Studio executasse códigos shell do windows que no caso “sqlcmd”.

Obrigado pessoal!!!!

Patrocínio Maia