Get notified when user with administrative privileges logs in

Question

I have less than a handful of users that have sysadmin role membership. I would like to be notified (emailed) when one of them logs in. Is creating a trigger the best way or is there some kind of built-in function in SQL Server or maybe a SQL Server Agent-Alert that can be used?

I could build a job looking at:

SELECT name 
FROM master.sys.server_principals 
WHERE IS_SRVROLEMEMBER ('sysadmin',name) = 1 and name not like '%NT%'

or something of that nature but that would mean the job would have to run every second...

Shaulinator · Accepted Answer · 2018-08-07T18:17:12.250

If you are looking to be NOTIFIED INSTANTLY AND CONSISTENTLY when a person with a specific permission logs in, Triggers are the best route.

If you want an audit of successful logins or failed logins, SQL Audit is your best route.

You can see a similar question here by Sergey on Stack Exchange.

There are other methods to achieve this goal, but it strictly depends on the requirements.

You can query for active sessions and join to system tables to find users active, you can audit who successfully logged in or not with the audit events and then correlate to the system tables too. (This becomes a retroactive or historical view, not a real time.) You can also use tracing / extended events to obtain this information and schedule a job to run off of. Here's an example of the Extended Events Logon Capture by Steve Jones on Voice of the DBA.

If you needed it instantly when the person logs in and consistently, triggers are your best resolution.

MSDN on Logon Triggers.

Here is an example of creating a LOGON trigger for all Sysadmin.

CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER
FOR LOGON
AS
DECLARE @UserInfo VARCHAR(255);
SET @UserInfo = (SELECT SYSTEM_USER);
IF @UserInfo = (   SELECT TOP 1 p.name AS [loginname]
                   FROM   sys.server_principals p
                   JOIN   sys.syslogins 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
    PRINT 'Hello';
END;
GO

You may want to create a way to avoid hitting the trigger, please see this answer if you want to incorporate it.

Note: Be careful of logon triggers, you don't want to lock yourself out.

Get notified when user with administrative privileges logs in

1 Answers1