SQL Server Tüm Veritabanlarında Otomatik Audit Oluşturmak

SQL Server’da bir Instance üzerinde tüm veritabanlarında, tüm kullanıcıların yapmış olduğu belli işlemleri kaydetmek isteyebilirsiniz. Bunun için Audit özelliğini kullanmanız gerekir. Audit hakkında detaylı bilgi almak için SQL Server Audit Oluşturmak isimli makaleyi okuyabilirsiniz. Bir instance üzerinde çok sayıda veritabanı olduğunda bunları manuel biçimde oluşturmak oldukça zaman alacaktır. Ayrıca bazı şeyleri gözden kaçırma ihtimali de oluşturacaktır. Bu nedenle TSQL ile tüm veritabanlarında bunları oluşturmak daha efektif olacaktır. Bununla ilgili olarak yazdığım Script aşağıdadır. Ben örnek olarak Audit adlarını veritabanı adlarıyla birebir aynı aldım. Filepath olarak Data dosyalarının default path’ini belirledim. Maximum dosya boyutu olarak 10 MB ve Maximum dosya sayısı olarak 1 belirledim. Siz kendi ihtiyaçlarınıza göre bu değerleri şekillendirebilirsiniz.

DECLARE @DatabaseName NVARCHAR(1000)
DECLARE @sqltext NVARCHAR(MAX)
DECLARE AuditCursor CURSOR FOR
SELECT name FROM sys.databases WHERE name NOT IN (SELECT name FROM sys.server_audits ) AND database_id > 4
OPEN AuditCursor
FETCH NEXT FROM AuditCursor INTO @DatabaseName
WHILE @@FETCH_STATUS=0
BEGIN
SET @sqltext=
'CREATE SERVER AUDIT '+ @DatabaseName +'
TO FILE 
(	FILEPATH = N''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA''
	,MAXSIZE = 10 MB
	,MAX_FILES = 1
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
)'

EXEC sp_executesql @sqltext

SET @sqltext=
'USE [' + @DatabaseName +'] 
CREATE DATABASE AUDIT SPECIFICATION ['+ @DatabaseName +'] FOR SERVER AUDIT [' + @DatabaseName + ']'

EXEC sp_executesql @sqltext

SET @sqltext=

'USE [' + @DatabaseName +'] 
DECLARE @AuditScript TABLE (ID INT IDENTITY,AuditScript NVARCHAR(MAX))
DECLARE @ID INT=1
DECLARE @sqltext NVARCHAR(MAX)
INSERT @AuditScript
 
SELECT ''ALTER DATABASE AUDIT SPECIFICATION ['' + DB_NAME() + ''] ADD (UPDATE ON DATABASE::['' + DB_NAME() + ''] BY [''+ name + ''])'' 
FROM sys.database_principals 
WHERE type=''S'' AND name NOT IN (''guest'',''INFORMATION_SCHEMA'',''sys'')

UNION ALL

SELECT ''ALTER DATABASE AUDIT SPECIFICATION ['' + DB_NAME() + ''] ADD (DELETE ON DATABASE::['' + DB_NAME() + ''] BY [''+ name +''])'' 
FROM sys.database_principals 
WHERE type=''S'' AND name NOT IN (''guest'',''INFORMATION_SCHEMA'',''sys'')

WHILE @ID<=(SELECT COUNT(*) FROM @AuditScript)
BEGIN
SET @sqltext=(SELECT AuditScript FROM @AuditScript WHERE ID=@ID)
EXEC sp_executesql @sqltext
SET @ID=@ID +1
END

SET @sqltext=N''ALTER DATABASE AUDIT SPECIFICATION ''  + DB_NAME() + '' WITH (STATE=ON) ''
EXEC sp_executesql @sqltext
'

EXEC sp_executesql @sqltext

SET @sqltext='ALTER SERVER AUDIT [' + @DatabaseName + ']  WITH (STATE=ON)'
EXEC sp_executesql @sqltext

FETCH NEXT FROM AuditCursor INTO @DatabaseName
END
CLOSE AuditCursor
DEALLOCATE AuditCursor