June 20, 2003 at 6:42 am
Hi
I am trying to add a trigger to the sysxlogins and/or syslogins table to catch when the SA password is changed.
This is returning the error
Server: Msg 229, Level 14, State 5, Procedure PasswordChange, Line 65535
CREATE TRIGGER permission denied on object 'sysxlogins', database 'master', owner 'dbo'.
I have tried logging in as the SA, but get the same error returned.
The question is, can this be done and if so how?
June 20, 2003 at 6:59 am
In order to be able to update the system tables, you have to use sp_configure to allow updates. You'll have to set SQL Server to show advanced options, first.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_3wmr.asp
However, you'd probably be better off modifying sp_password or setting up a trace. Triggers on system tables aren't supported by Microsoft. For that matter, neither is a modified sp_password, but you get the idea. The trace is what's been recommended here in the past.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
June 20, 2003 at 8:31 am
Thanks for the help, I have decided to go with altering the SP_PASSWORD procedure.
June 20, 2003 at 3:24 pm
Also, if I remember Karen Delaney's talk at PASS correctly, triggers are not guarenteed to fire on system tables.
Steve Jones
January 27, 2005 at 4:09 am
Hi Exon
I am trying to do the same. Now and then someone changes sa password, ehich throws the replication and application authentication to fall over. If you have done it already then is it possible if you can share the sp_password proc, or if you can guide how you did it.
Many thanks
K M Dhariwal
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply