en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize
אוג7

Written by: ronen ariely
07/08/2019 04:38 RssIcon

This post includes sample code for step by step: (1) create database. (2) CREATE TABLE. (3) create folder for the Audit logs. (4) CREATE SERVER AUDIT. (5) CREATE SERVER AUDIT SPECIFICATION. (6) CREATE DATABASE AUDIT SPECIFICATION. (7) Read the log file.

/***********************************************************
** SQL Server Security:
**
************************************************************/
 
create database AuditTestDB
GO
USE AuditTestDB
GO
CREATE TABLE T(id int)
GO
 
/*********** Auditing activities on table **********************/
-- 1. create folder for the Audit logs:
-- C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\RonenArielyAudit
-- 2. give full permissions to the SQL Server service user on the folder
-- 3. create server audit
USE [master]
GO
CREATE SERVER AUDIT [RonenArielyAudit]
TO FILE (
    FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\RonenArielyAudit'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
GO
SELECT * FROM sys.server_audits
GO
-- 4. Started the server audit
ALTER SERVER AUDIT [RonenArielyAudit] WITH (STATE = ON)
GO
CREATE SERVER AUDIT SPECIFICATION [RonenArielyServerAuditSpecification]
    FOR SERVER AUDIT [RonenArielyAudit]
    ADD (FAILED_LOGIN_GROUP)
    ,ADD (SUCCESSFUL_LOGIN_GROUP)
GO
ALTER SERVER AUDIT SPECIFICATION [RonenArielyServerAuditSpecification]
    WITH (STATE = ON)
GO
SELECT * FROM sys.server_audit_specifications
GO
SELECT * FROM sys.server_audit_specification_details
GO
-- 6. To audit database level activitities create DATABASE AUDIT SPECIFICATION
--    (this configure what you want to log)
USE AuditTestDB
GO
CREATE DATABASE AUDIT SPECIFICATION [RonenArielyDatabaseAuditSpecification]
    FOR SERVER AUDIT [RonenArielyAudit]
    -- You can add multiple rules to audit
    ADD (INSERT ON OBJECT::[dbo].[T] BY [dbo]),
    ADD (DELETE ON OBJECT::[dbo].[T] BY [dbo]),
    ADD (SELECT ON OBJECT::[dbo].[T] BY [dbo])
    WITH (STATE = OFF)
GO
ALTER DATABASE AUDIT SPECIFICATION [RonenArielyDatabaseAuditSpecification]
    WITH (STATE = ON)
GO
---------------- CREATE SOME ACTIVITIES -------------
SELECT * FROM T
GO
INSERT T(id) values (1),(2),(3)
GO
SELECT * FROM T
GO
DELETE T WHERE id = 2
GO
SELECT * FROM T
GO
-----------------------------------------------------
-- 7. Read the log file using SSMS
--    Right-click the audit log that you want to view and select View Audit Logs.
-- 8. read the log file using T-SQL (open the folder and find the log file name)
SELECT * FROM sys.server_file_audits
GO
SELECT * FROM sys.fn_get_audit_file (
    'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\RonenArielyAudit_B9933D11-B87A-4A7C-B716-EA481517D530_0_132096201622720000.sqlaudit',default,default
); 
GO
-- Note! it might take several secons time untill the information is added ot the Audit log.
 
 
/****************** CLEAN ********************/
ALTER DATABASE AUDIT SPECIFICATION [RonenArielyDatabaseAuditSpecification]
    WITH (STATE = OFF)
GO
DROP DATABASE AUDIT SPECIFICATION [RonenArielyDatabaseAuditSpecification]
GO
USE master
GO
ALTER SERVER AUDIT SPECIFICATION [RonenArielyServerAuditSpecification]
    WITH (STATE = OFF)
GO
DROP SERVER AUDIT SPECIFICATION [RonenArielyServerAuditSpecification]; 
GO
ALTER SERVER AUDIT [RonenArielyAudit] WITH (STATE = OFF)
GO 
DROP SERVER AUDIT [RonenArielyAudit]; 
GO

  

More information


-- SQL Server Security:
-- https://docs.microsoft.com/en-us/sql/relational-databases/security/security-center-for-sql-server-database-engine-and-azure-sql-database
-- Azure SQL Database security capabilities
-- https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview
-- Azure SQL Database audit
-- https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing