locked
Email Notification for created and modified logins in SQL Server 2008 RRS feed

  • Question

  • Hi all,

    please I need a way to send email notification if new SQL or Windows login created or modified in sql server 2008, I tried to create a trigger on sys.server_principals view, but I found that I can't create a trigger on system object. 

    any Ideas please?

    Thanks in advance,

    Faris ALMasri

    Tuesday, February 4, 2014 10:35 AM

Answers

  • Tuesday, February 4, 2014 11:00 AM
  • You can also create DDL Trigger to do so...(Not tested,PLEASE TEST IT)

    CREATE TRIGGER ddl_trackloginchanges
    ON ALL SERVER
    FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
    AS
    		
    	DECLARE @data XML;
    	DECLARE @text Nvarchar(max);
    	DECLARE @user Nvarchar(max);
    	
    	SET @data = EVENTDATA();
        SET @text = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
    	SET @user = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');
    	
    	---- Set the email data
    	SET @mailSubject = 'New SQL Login Created On: ' + @@SERVERNAME;
    	SET @mailBody = 'A new login was detected on the SQL Server: <b>'  + @@SERVERNAME + '
    </b>' +  
    					'User name: <b>' + ISNULL(@text, 'Null User Name') + '
    </b>' +
    					'Added/Modified by user: <b>' + ISNULL(@user, 'Null') +  '
    </b>' +  +					   
    					'On date: <b>' + CONVERT(nvarchar, getdate(), 13) + '
    
    ' +  
    					'Please verify why this login was created!' + '
    </b>' ;
    
    	--Send the mail
    	EXEC msdb.dbo.sp_send_dbmail
    		@recipients=N'yourid@domain.name',
    		@subject=@mailSubject,
    		@body = @mailBody,
    		@profile_name = 'profilename',
    		@body_format = HTML;


    Tuesday, February 4, 2014 1:43 PM

All replies

  • Tuesday, February 4, 2014 11:00 AM
  • You can also create DDL Trigger to do so...(Not tested,PLEASE TEST IT)

    CREATE TRIGGER ddl_trackloginchanges
    ON ALL SERVER
    FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN
    AS
    		
    	DECLARE @data XML;
    	DECLARE @text Nvarchar(max);
    	DECLARE @user Nvarchar(max);
    	
    	SET @data = EVENTDATA();
        SET @text = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
    	SET @user = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');
    	
    	---- Set the email data
    	SET @mailSubject = 'New SQL Login Created On: ' + @@SERVERNAME;
    	SET @mailBody = 'A new login was detected on the SQL Server: <b>'  + @@SERVERNAME + '
    </b>' +  
    					'User name: <b>' + ISNULL(@text, 'Null User Name') + '
    </b>' +
    					'Added/Modified by user: <b>' + ISNULL(@user, 'Null') +  '
    </b>' +  +					   
    					'On date: <b>' + CONVERT(nvarchar, getdate(), 13) + '
    
    ' +  
    					'Please verify why this login was created!' + '
    </b>' ;
    
    	--Send the mail
    	EXEC msdb.dbo.sp_send_dbmail
    		@recipients=N'yourid@domain.name',
    		@subject=@mailSubject,
    		@body = @mailBody,
    		@profile_name = 'profilename',
    		@body_format = HTML;


    Tuesday, February 4, 2014 1:43 PM
  • Thank you Andreas, after declaring @Mailsubject and @mailbody, it works :)
    Wednesday, February 5, 2014 5:07 AM
  • thank you so much Satheesh :)
    Wednesday, February 5, 2014 5:08 AM