mirror of
https://dev.azure.com/effectory/Survey%20Software/_git/Cloud%20Engineering
synced 2026-02-27 18:52:18 +01:00
135 lines
4.2 KiB
Transact-SQL
135 lines
4.2 KiB
Transact-SQL
use master
|
|
go
|
|
|
|
ALTER CREDENTIAL [https://sqlauditlogeffectory.blob.core.windows.net/azurereporting2]
|
|
WITH IDENTITY='SHARED ACCESS SIGNATURE',
|
|
SECRET = 'sp=racwdl&st=2024-04-09T13:43:40Z&se=2035-04-19T21:43:40Z&spr=https&sv=2022-11-02&sr=c&sig=zJqPI62bJP%2F94xtQPyDMFbyStgy0YXuGySqW6VSbMLo%3D'
|
|
GO
|
|
|
|
|
|
|
|
CREATE SERVER AUDIT [AZUREREPORTING2_SERVER_AUDIT] TO URL ( PATH ='https://sqlauditlogeffectory.blob.core.windows.net/azurereporting2' , RETENTION_DAYS = 365)
|
|
GO
|
|
|
|
|
|
-- Create server specification
|
|
CREATE SERVER AUDIT SPECIFICATION [AZUREREPORTING2_SERVER_AUDIT_SPEC]
|
|
FOR SERVER AUDIT [AZUREREPORTING2_SERVER_AUDIT]
|
|
ADD (FAILED_LOGIN_GROUP),
|
|
ADD (LOGOUT_GROUP),
|
|
ADD (SUCCESSFUL_LOGIN_GROUP)
|
|
WITH (STATE=ON);
|
|
GO
|
|
|
|
-- Set state on
|
|
ALTER SERVER AUDIT [AZUREREPORTING2_SERVER_AUDIT] WITH (STATE = ON);
|
|
GO
|
|
|
|
USE [ReportServer]
|
|
GO
|
|
|
|
CREATE DATABASE AUDIT SPECIFICATION [ReportServer_audit]
|
|
FOR SERVER AUDIT [AZUREREPORTING2_SERVER_AUDIT]
|
|
ADD (DATABASE_OBJECT_ACCESS_GROUP),
|
|
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
|
|
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
|
|
ADD (BATCH_COMPLETED_GROUP),
|
|
ADD (BACKUP_RESTORE_GROUP),
|
|
ADD (BATCH_STARTED_GROUP),
|
|
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (USER_CHANGE_PASSWORD_GROUP),
|
|
ADD (USER_DEFINED_AUDIT_GROUP),
|
|
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
|
|
ADD (DATABASE_OPERATION_GROUP),
|
|
ADD (LEDGER_OPERATION_GROUP),
|
|
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
|
|
ADD (SENSITIVE_BATCH_COMPLETED_GROUP),
|
|
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
|
|
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
|
|
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
|
|
ADD (DATABASE_CHANGE_GROUP),
|
|
ADD (DATABASE_OBJECT_CHANGE_GROUP),
|
|
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
|
|
ADD (AUDIT_CHANGE_GROUP),
|
|
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
|
|
ADD (DATABASE_LOGOUT_GROUP),
|
|
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
|
|
ADD (DBCC_GROUP),
|
|
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
|
|
WITH (STATE = ON)
|
|
GO
|
|
|
|
USE [ReportServerTempDB]
|
|
GO
|
|
|
|
CREATE DATABASE AUDIT SPECIFICATION [ReportServerTempDB_audit]
|
|
FOR SERVER AUDIT [AZUREREPORTING2_SERVER_AUDIT]
|
|
ADD (DATABASE_OBJECT_ACCESS_GROUP),
|
|
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
|
|
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
|
|
ADD (BATCH_COMPLETED_GROUP),
|
|
ADD (BACKUP_RESTORE_GROUP),
|
|
ADD (BATCH_STARTED_GROUP),
|
|
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (USER_CHANGE_PASSWORD_GROUP),
|
|
ADD (USER_DEFINED_AUDIT_GROUP),
|
|
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
|
|
ADD (DATABASE_OPERATION_GROUP),
|
|
ADD (LEDGER_OPERATION_GROUP),
|
|
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
|
|
ADD (SENSITIVE_BATCH_COMPLETED_GROUP),
|
|
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
|
|
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
|
|
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
|
|
ADD (DATABASE_CHANGE_GROUP),
|
|
ADD (DATABASE_OBJECT_CHANGE_GROUP),
|
|
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
|
|
ADD (AUDIT_CHANGE_GROUP),
|
|
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
|
|
ADD (DATABASE_LOGOUT_GROUP),
|
|
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
|
|
ADD (DBCC_GROUP),
|
|
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
|
|
WITH (STATE = ON)
|
|
GO
|
|
|
|
USE [SSISDB]
|
|
GO
|
|
|
|
CREATE DATABASE AUDIT SPECIFICATION [SSISDB_audit]
|
|
FOR SERVER AUDIT [AZUREREPORTING2_SERVER_AUDIT]
|
|
ADD (DATABASE_OBJECT_ACCESS_GROUP),
|
|
ADD (SCHEMA_OBJECT_ACCESS_GROUP),
|
|
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
|
|
ADD (BATCH_COMPLETED_GROUP),
|
|
ADD (BACKUP_RESTORE_GROUP),
|
|
ADD (BATCH_STARTED_GROUP),
|
|
ADD (DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (USER_CHANGE_PASSWORD_GROUP),
|
|
ADD (USER_DEFINED_AUDIT_GROUP),
|
|
ADD (SCHEMA_OBJECT_CHANGE_GROUP),
|
|
ADD (DATABASE_OPERATION_GROUP),
|
|
ADD (LEDGER_OPERATION_GROUP),
|
|
ADD (APPLICATION_ROLE_CHANGE_PASSWORD_GROUP),
|
|
ADD (SENSITIVE_BATCH_COMPLETED_GROUP),
|
|
ADD (DATABASE_OWNERSHIP_CHANGE_GROUP),
|
|
ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
|
|
ADD (SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP),
|
|
ADD (DATABASE_PRINCIPAL_IMPERSONATION_GROUP),
|
|
ADD (DATABASE_CHANGE_GROUP),
|
|
ADD (DATABASE_OBJECT_CHANGE_GROUP),
|
|
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
|
|
ADD (AUDIT_CHANGE_GROUP),
|
|
ADD (FAILED_DATABASE_AUTHENTICATION_GROUP),
|
|
ADD (DATABASE_LOGOUT_GROUP),
|
|
ADD (SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP),
|
|
ADD (DBCC_GROUP),
|
|
ADD (DATABASE_PERMISSION_CHANGE_GROUP)
|
|
WITH (STATE = ON)
|
|
GO
|