Trigger to alert when database server admin roles are added

ALTER TRIGGER DDL_AUDIT_Logins ON ALL SERVER

FOR ADD_SERVER_ROLE_MEMBER

,DDL_GDR_SERVER_EVENTS

,DROP_SERVER_ROLE_MEMBER AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (

EType NVARCHAR(max)

,EObject VARCHAR(100)

,EDate DATETIME

,EUser VARCHAR(100)

,ECommand NVARCHAR(max)

);

DECLARE @EType NVARCHAR(max);

DECLARE @ESchema NVARCHAR(max);

DECLARE @DBName VARCHAR(100);

DECLARE @Subject VARCHAR(200);

DECLARE @EObject VARCHAR(100);

DECLARE @EObjectType VARCHAR(100);

DECLARE @EMessage NVARCHAR(max);

DECLARE @ETSQL NVARCHAR(max);

--DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',

'nvarchar(max)')

,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',

'nvarchar(max)')

,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',

'nvarchar(max)')

,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',

'nvarchar(max)')

,@DBName = EVENTDATA().value('

(/EVENT_INSTANCE/DatabaseName)[1]',

'nvarchar(max)')

,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',

'nvarchar(max)');

INSERT INTO @EventsTable

SELECT @EType

,@EObject

,GETDATE()

,SUSER_SNAME()

,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '

+ Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject +

CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' +

@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'richlemmermann@gmail.com'

,@body = @EMessage,

@profile_name = 'MAILSERVER'

,@subject = @Subject

--,@body_format = 'HTML'

;

SET NOCOUNT OFF;

--https://solutioncenter.apexsql.com/auditing-security-changes-in-sql-server-2/

Setup Alerts for AlwaysOn

(totally swiped from: https://www.mssqltips.com/sqlservertip/3489/configure-sql-server-alerts-and-notifications-for-alwayson-availability-groups/)

When the replica changes role or a failover to secondary replica happens
When data movement is suspended
When data movement resumed

So lets proceed to create the alerts for each of the scenarios above. By now creating alerts should be a breeze for you. But if this the first time you are creating one, don't fret. You have an option of doing it via the SQL Server Agent GUI or by scripting.

The easiest way to do this is via scripting, but you may choose to do this using SQL Server Management Studio (SSMS). In SSMS, open Object Explorer, go to SQL Server Agent > Alerts > New Alerts. This should open up a new alert dialog window for you.

But in this tip, I am going to show how to do this via scripting.

Create SQL Server Alert for AlwaysOn Role Change

EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Role Change',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]', 
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

The message_id is the error id for the role change or failover event, for this event the value is 1480.

For the job_id value, you have an option to create a SQL Server Agent Job that will run when this alert is fired. The job_id would be the job_id for the SQL Server Agent Job. You can run sp_help_job to get a list of SQL Agent jobs and the job_id value.

Create SQL Server Alert for AlwaysOn Data Movement Suspended

To setup an alert for the data movement suspended, you can run this code, but note we are looking for message_id 35264.

EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Data Movement Suspended',
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0, 
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Create SQL Server Alert for AlwaysOn Data Movement Resumed

To setup an alert for the data movement resumed, you can run this code, but note we are looking for message_id 35265.

EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Data Movement Resumed',
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Setup SQL Server Alert Notifications

After you have created the alerts, the next thing to do is to add notifications to your alerts, so you get alerted if the scenario described above happens. You may again do this via the SSMS or alternatively update and execute the script below based on the values in your environment. This would need to be done for each alert that you create. Also, you will need to setup Database Mail and Operators.

EXEC msdb.dbo.sp_add_notification
@alert_name = N'AlwaysOn - Role Change',
@operator_name = N'myemail@email.com',
@notification_method = <Notification,INT,1>;
GO

When doing this via the SSMS, open your newly created Alerts, go to Response on the left menu and check the Notify Operators checkbox. On the name of your Operator, check the correct email checkbox.

Alert if a SQLAgent Job is Enabled or Disabled

USE msdb

CREATE TRIGGER tr_SysJobs_enabled

ON sysjobs

FOR UPDATE AS

----------------------------------------------------------------------------

-- Object Type : Trigger

-- Object Name : msdb..tr_SysJobs_enabled

-- Description : trigger to email DBA team when a job is enabled or disabled

-- Author : www.mssqltips.com

-- Date : July 2009

----------------------------------------------------------------------------

SET NOCOUNT ON

DECLARE @UserName VARCHAR(50),

@HostName VARCHAR(50),

@JobName VARCHAR(100),

@DeletedJobName VARCHAR(100),

@New_Enabled INT,

@Old_Enabled INT,

@Bodytext VARCHAR(200),

@SubjectText VARCHAR(200),

@Servername VARCHAR(50)

SELECT @UserName = SYSTEM_USER, @HostName = HOST_NAME()

SELECT @New_Enabled = Enabled FROM Inserted

SELECT @Old_Enabled = Enabled FROM Deleted

SELECT @JobName = Name FROM Inserted

SELECT @Servername = @@servername

-- check if the enabled flag has been updated.

IF @New_Enabled <> @Old_Enabled

BEGIN

IF @New_Enabled = 1

BEGIN

SET @bodytext = 'User: '+@username+' from '+@hostname+

' ENABLED SQL Job ['+@jobname+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)

SET @subjecttext = @Servername+' : ['+@jobname+

'] has been ENABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)

END

IF @New_Enabled = 0

BEGIN

SET @bodytext = 'User: '+@username+' from '+@hostname+

' DISABLED SQL Job ['+@jobname+'] at '+CONVERT(VARCHAR(20),GETDATE(),100)

SET @subjecttext = @Servername+' : ['+@jobname+

'] has been DISABLED at '+CONVERT(VARCHAR(20),GETDATE(),100)

END

SET @subjecttext = 'SQL Job on ' + @subjecttext

-- send out alert email

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'mail profile', --<<< insert your Mail Profile here

@recipients = 'email@sss.com', --<<< insert your team email here

@body = @bodytext,

@subject = @subjecttext

--https://www.mssqltips.com/sqlservertip/1803/auto-alert-for-sql-agent-jobs-when-they-are-enabled-or-disabled/

END