SQL Server Auditing

SQL Server da auditing işlemini profiler arayüzü ile yapabiliyoruz. Tüm hareketleri, ilgili kriterleri seçerek (Örn.: Login/Logout, database operation vs.) oluşturulan trace altında toplayabiliriz. SQL server bu trace leri sp_trace_setevent adı alında konfigure etmekte, trace id ve event id lerin ne anlama geldiklerini aşağıdaki Microsoft linkten ulaşabiliriz.


http://msdn.microsoft.com/en-us/library/ms186265.aspx

SQL SERVER AUDITING

Şimdi aşağıda hazırlanmış default olarak nitelendirdiğim bir query i paylaşıyor olacağım.

/****************************************************/
/* Created by: SQL Server 2008 Profiler (Anatoli) */
/* Date: 01/05/2011 11:43:53 AM */
/****************************************************/

-- Create a Queue

declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 20
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 2, N'D:\AnatoliSQLTrace\audittrace201101011111', @maxfilesize, NULL

if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 2, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 2, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
exec sp_trace_setevent @TraceID, 13, 12, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler – 9*4*d***2-2****c-**51-8***4-46**6*bv**5'

-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go

Start Command (Bu trace i start etmek için aşağıdaki komutu kullanıyoruz);
--exec sp_trace_setstatus 2, 2

Yüksek sayıda kayıt tutan dosyaların SQL Server'a yüklenmesi
Yüksek sayıda kayıt tutan bir dosyanız var ve fakat SQL Query Analyzer da bu kayıtları insert etmeye çalıştığınızda kısa bir süre sonra warning veriyor ise telaşlanmayın. Bunun comment dizilimi ile kolay bir yolu var.

OSQL C:\Program Files\Microsoft SQL Server\100\Tools\Binn dizinin altında bulunan osql.exe ile bu mümkün. İşletim sistemi üzerinde yeni bir cmd açarak aşağıdaki syntax ile bu işi yapabilirsiniz.
osql -d database name -U login name -P Password -i D:\filename.sql -o D:\filenamelog.txt

Bu komuttan sonra ekranda sayısal veriler ile matrix canlandırabilirsiniz. :)
Bu komutun syntax ları aşağıda gibidir. Umarım işinize yarar.

osql -U login_id [-e] [-E] [-p] [-n]
[-d db_name] [-Q "query"] [-q "query"]
[-c cmd_end] [-h headers] [-w column_width]
[-s col_separator] [-t time_out] [-m error_level] [-I] [-L] [-?] [-r {0 | 1}]
[-H wksta_name] [-P password] [-R] [-S server_name] [-i input_file] [-o output_file] [-u] [-a packet_size][-b] [-O] [-l time_out]