sa password change alert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

sa password change alert

Author

Topic

Wrangler
Starting Member

35 Posts

Posted - 2011-05-18 : 17:31:48

Is it possible to create an alert when the sa password changes for SQL 2008, 2005, and 2000?

Thanks,

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-05-18 : 17:40:14

If you periodically query syslogins or sys.sql_logins and compare the password hash you can detect a change. It may be possible with Extended Events, but not sure of another method.

This of course begs the questions 1) why is sa being changed without your knowledge? and 2) who is using it that they have to change it? sa should never be used for normal SQL Server access.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-05-18 : 19:20:19

I agree with robvolk - why is the sa login being used at all?

On systems that I build, the sa login is given a randomly generated 20 character strong password. I save that password in a password safe and never use it once the system has been setup and configured. The only time it would ever be required is if I somehow get locked out of SQL Server - and the only way that could happen is if I were removed from the AD group that I belong to that is added as a sysadmin, or there is no way to authenticate on the domain.

Jeff

Wrangler
Starting Member

35 Posts

Posted - 2011-05-18 : 19:22:32

Its really just an extra security measure to generate an alert if the sa account has been changed. thanks.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-05-20 : 12:06:05

You could create a server level DDL trigger.[CODE]CREATE TRIGGER [login_changes]
ON ALL SERVER
FOR ALTER_LOGIN
AS
BEGIN
declare
@data XML = EventData(),
@subject varchar(100),
@body varchar(max);

set @subject = @@SERVERNAME + ' - ' + SUSER_NAME() + ' - Check Logins!'

SET @body =
cast((select SUSER_NAME()) as varchar(max)) + ' Server Name : ' + ( @@SERVERNAME)+ ' changed a login.' + char(13) + char(10) +
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') + char(13) + char(10) +
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)');

EXEC msdb.dbo.sp_send_dbmail <details, details, etc.>
END[/CODE]

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources