I was helping someone set up some monitoring in their database and they were asking about being notified when someone with administrative privileges logs into SQL Server. While many of you know that I try to use the right tool for the right job, some may cringe when I say triggers can help out in this scenario.
Now, there are other methods here but the goal was to be notified when someone logs into your SQL Server with administrative privileges. Triggers will consistently fire when this event occurs and allows you to perform an action based on this event.
If I wanted to retain a record or history of these events and not notify, I’d do this differently. Steve Jones on Voice of the DBA has an example of using Extended Events to capture logon events. Likewise, you can use use the Audit feature in SQL Server to capture logon events which Brian Kelley on MS SQL Tips demonstrates. After the information has been captured, you can run a job periodically on the data collected to report or alert off of.
So once more, this is to consistently and reliably (unless your mail server fails you) notify you when a user with administrative privileges logs in.
CREATE TRIGGER [LogonAuditTrigger] ON ALL SERVER
FOR LOGON
AS
DECLARE @UserInfo VARCHAR(255);
DECLARE @Server VARCHAR(255);
SET @UserInfo = (SELECT SYSTEM_USER);
SET @Server = (SELECT @@SERVERNAME);
IF @UserInfo = (
SELECT TOP 1
[p].[name] AS [loginname]
FROM [sys].[server_principals] AS [p]
JOIN [sys].[syslogins] AS [s] ON [p].[sid] = [s].[sid]
WHERE [p].[type_desc] IN('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
--Logins that are not process logins
AND [p].[name] NOT LIKE '##%'
--Logins that are sysadmins
AND [s].[sysadmin] = 1
AND [p].[name] = @UserInfo)
BEGIN
DECLARE @TBody VARCHAR(MAX);
DECLARE @TSubject VARCHAR(MAX);
SELECT @TBody = @UserInfo + 'has logged into ' + @Server + ' with administrative privileges';
SELECT @TSubject = @UserInfo + 'has logged into ' + @Server + ' with administrative privileges';
EXEC [msdb].[dbo].[sp_send_dbmail]
@profile_name = 'Main DB Mail profile'
, @recipients = 'mail@mail.com'
, @subject = @TSubject
, @Body = @TBody
END;
GO
In the code above, you would replace the bottom mail server information with applicable emails and profiles.
As always, test this in a dev environment and propagate up through the environments utilizing whatever change control methods your team participates in.