Log SA access and send an email

A client has reported that someone is entering a setup form and editing something as the 'sa' user, and asked if there is any way to track it. 

I'd be interested if your approach would have been different than mine, feel free to chime in.

The script below will email every time a user logs on as the 'sa' user and report the machine name that they logged in from

 

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

USE DYNAMICS
GO
 
IF OBJECT_ID ('dbo.ddt_ACTIVITY_INS','TR') IS NOT NULL
   DROP TRIGGER dbo.ddt_ACTIVITY_INS 
GO
  
CREATE TRIGGER dbo.ddt_ACTIVITY_INS 
    ON ACTIVITY
    FOR INSERT
       
AS
   
 
--Written by Steve Gray 941-747-3669
DECLARE @hostname varchar(255)
declare @body varchar(200)
declare @cc as varchar(200)
set @cc = ''
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR
    SELECT convert(varchar(255), SERVERPROPERTY('MACHINENAME') )
        FROM inserted ins
        where ins.USERID = 'sa'
   
OPEN curName
WHILE 1=1
BEGIN
    FETCH NEXT FROM curName INTO @hostname
    if @@fetch_status <> 0 begin
        break
    end
     
    select @Body = 'the sa use has just logged on from the ' + @hostname + ' workstation'
 
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Default profile',
        @recipients = 'steve@steve.com',
        @copy_recipients = @cc,
        @body = @Body,
        @body_format = 'HTML',
        @subject = 'SA access'
END
CLOSE curName
DEALLOCATE curName
 
 
   
 
GO

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences