Ideally, you want to go with Windows Authentication for all your users in SQL Server whenever possible. In the real world, most applications rely on SQL logins. So it happens rather frequently that when the password for a SQL login that is critical to an application is changed, that application breaks. Now, granted, if you are doing things right you have tight controls in place around who can modify passwords, but in reality, this stuff happens all the time.
In this post, I will present you with a script that will create a server-level DDL trigger that will send an email whenever one of a set of deemed critical SQL accounts is modified.
Note that DDL triggers were made available initially in SQL 2005, so this script won’t work on SQL 2000.
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'trg_notifyPasswordChange') DROP TRIGGER trg_notifyPasswordChange ON ALL SERVER; GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER trg_notifyPasswordChange ON ALL SERVER FOR ALTER_LOGIN AS DECLARE @loginName AS sysname DECLARE @serverName AS sysname DECLARE @timeOfEvent AS datetime DECLARE @loginAffected AS sysname DECLARE @bodyOfEmail AS nvarchar(2000) SELECT @loginName = EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(256)') , @serverName = EVENTDATA().value('(/EVENT_INSTANCE/ServerName)[1]', 'sysname') , @timeOfEvent = EVENTDATA().value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') , @loginAffected = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname') IF @loginAffected IN ('sa', 'sqlaccount1', 'sqlaccount2') -- List of logins goes here -- modify at your discretion BEGIN SELECT @bodyOfEmail = @loginName + ', ' + @serverName + ', ' + CONVERT(VARCHAR(20), @timeOfEvent, 100) + ', ' + @loginAffected -- You will have to modify profile_name and the recipients on the next statement EXEC msdb.dbo.sp_send_dbmail @profile_name = '<your_dbmail_profile_here>' , @recipients = '<your_recipient_list_here>' , @subject = 'Critical SQL Login Password Changed!' , @body = @bodyOfEmail END GO
Sorry about the formatting — I am still fighting with the formatting WP plug-in. As always, comments are more than welcome. Hope this helps!
UPDATE As Tim Benninghoff commented below, this trigger will fire whenever ANY changes are made to ANY of the critical SQL logins. Please keep in mind that you will likely get false notifications.
Bear in mind that this trigger isn’t strictly for password changes. You’ll also receive notification if the default database or language is changed for those logins, if the login is disabled or enabled, etc. Basically, any changes that you might make on the General or Status pages of the Login Properties in SSMS will fire this trigger.
[...] This post was mentioned on Twitter by afernandez and Tampa Bay SSUG. Tampa Bay SSUG said: RT @afernandez: [New Blog Post] Server-level Trigger to Notify on SQL Password Changes http://bit.ly/42QG2c #SQLServer [...]