3

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...

3

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.

  • My only issue with triggers was that I would have to create a trigger for every Admin user I'm trying to monitor, which with a handful of users isn't too bad, i guess. Thank you. – Tomasz Aug 7 '18 at 18:02
  • @Tomasz - you'd only need a single logon trigger... it would simply send the notification whenever someone in the sysadmin role logs in. – Max Vernon Aug 7 '18 at 18:13
  • @Tomasz I updated my answer with code so you don't need to create this for all users. – Shaulinator Aug 7 '18 at 18:17
  • 1
    @Tomasz it happens when you tell it to happen. Example: You don't want finance to log into the database outside work hours so they don't impact maintenance. You place a logon trigger for people in that role so they can't log in during those times. Except, woops, you missed that where clause. So instead of finance not being able to log in after hours, no one can log in after hours. Mistakes can happen, and that's when you'd run into this issue. Make a plan, develop a proof of concept locally, progress through lower environments with QA, bring to change board, have a rollback process ready. – Shaulinator Aug 7 '18 at 18:56
  • 3
    And have a script ready to disable the logon trigger after connecting to the Dedicated Administrator Connection docs.microsoft.com/en-us/sql/database-engine/configure-windows/… – David Browne - Microsoft Aug 7 '18 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.