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 2000 Forums
 SQL Server Administration (2000)
 tracking password changes

Author  Topic 

rb1373
Yak Posting Veteran

93 Posts
Posted - 2005-04-07 : 11:51:16
Is there anyway to accurately track who or when a password for a SQL login (SQL autentication not Windows) changed? One of the passwords of my inherited service accounts changed and I don't know who did it. I have a general idea of when the change was made, but syslogins nor sysusers doesn't provide accurate update information. I'm thinking of placing a trigger on sysxlogins. Let me know if there is a better solution.

Thanks,
Ray

jason
Posting Yak Master

164 Posts
Posted - 2005-04-07 : 15:41:06
Sounds like you need to tighten your security.

You can't create a trigger on a system table (BOL). Perhaps the Lumigent will allow you to see who and when, but I'm not sure as I've never used it before.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2005-04-07 : 17:11:57
Lumigent's Log Explorer allows you to read a transaction log. Since master uses SIMPLE recovery model, there are no transaction log backups for you to view.

Tara
Go to Top of Page

jason
Posting Yak Master

164 Posts
Posted - 2005-04-07 : 17:15:25
a damn fine point...
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts
Posted - 2005-04-07 : 17:26:58
trace ?

rockmoose
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts
Posted - 2005-04-07 : 22:04:55
Here's a good security trace definition file if you're interested. Just setup a job to run this on startup. Stop the trace, load the file into a table, and restart the trace daily. You can learn all about how to run these traces in Books Online of course.


/****************************************************/
/* Created by: SQL Profiler                         */
/* Date: 04/07/2005  09:05:56 PM         */
/****************************************************/


-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 

-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

-- Client side File and Table cannot be scripted

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 104, 1, @on
exec sp_trace_setevent @TraceID, 104, 3, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 7, @on
exec sp_trace_setevent @TraceID, 104, 8, @on
exec sp_trace_setevent @TraceID, 104, 9, @on
exec sp_trace_setevent @TraceID, 104, 10, @on
exec sp_trace_setevent @TraceID, 104, 11, @on
exec sp_trace_setevent @TraceID, 104, 12, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 19, @on
exec sp_trace_setevent @TraceID, 104, 23, @on
exec sp_trace_setevent @TraceID, 104, 26, @on
exec sp_trace_setevent @TraceID, 104, 35, @on
exec sp_trace_setevent @TraceID, 104, 38, @on
exec sp_trace_setevent @TraceID, 104, 40, @on
exec sp_trace_setevent @TraceID, 105, 1, @on
exec sp_trace_setevent @TraceID, 105, 3, @on
exec sp_trace_setevent @TraceID, 105, 6, @on
exec sp_trace_setevent @TraceID, 105, 7, @on
exec sp_trace_setevent @TraceID, 105, 8, @on
exec sp_trace_setevent @TraceID, 105, 9, @on
exec sp_trace_setevent @TraceID, 105, 10, @on
exec sp_trace_setevent @TraceID, 105, 11, @on
exec sp_trace_setevent @TraceID, 105, 12, @on
exec sp_trace_setevent @TraceID, 105, 14, @on
exec sp_trace_setevent @TraceID, 105, 19, @on
exec sp_trace_setevent @TraceID, 105, 23, @on
exec sp_trace_setevent @TraceID, 105, 26, @on
exec sp_trace_setevent @TraceID, 105, 35, @on
exec sp_trace_setevent @TraceID, 105, 38, @on
exec sp_trace_setevent @TraceID, 105, 40, @on
exec sp_trace_setevent @TraceID, 107, 1, @on
exec sp_trace_setevent @TraceID, 107, 3, @on
exec sp_trace_setevent @TraceID, 107, 6, @on
exec sp_trace_setevent @TraceID, 107, 7, @on
exec sp_trace_setevent @TraceID, 107, 8, @on
exec sp_trace_setevent @TraceID, 107, 9, @on
exec sp_trace_setevent @TraceID, 107, 10, @on
exec sp_trace_setevent @TraceID, 107, 11, @on
exec sp_trace_setevent @TraceID, 107, 12, @on
exec sp_trace_setevent @TraceID, 107, 14, @on
exec sp_trace_setevent @TraceID, 107, 19, @on
exec sp_trace_setevent @TraceID, 107, 23, @on
exec sp_trace_setevent @TraceID, 107, 26, @on
exec sp_trace_setevent @TraceID, 107, 35, @on
exec sp_trace_setevent @TraceID, 107, 38, @on
exec sp_trace_setevent @TraceID, 107, 40, @on
exec sp_trace_setevent @TraceID, 108, 1, @on
exec sp_trace_setevent @TraceID, 108, 3, @on
exec sp_trace_setevent @TraceID, 108, 6, @on
exec sp_trace_setevent @TraceID, 108, 7, @on
exec sp_trace_setevent @TraceID, 108, 8, @on
exec sp_trace_setevent @TraceID, 108, 9, @on
exec sp_trace_setevent @TraceID, 108, 10, @on
exec sp_trace_setevent @TraceID, 108, 11, @on
exec sp_trace_setevent @TraceID, 108, 12, @on
exec sp_trace_setevent @TraceID, 108, 14, @on
exec sp_trace_setevent @TraceID, 108, 19, @on
exec sp_trace_setevent @TraceID, 108, 23, @on
exec sp_trace_setevent @TraceID, 108, 26, @on
exec sp_trace_setevent @TraceID, 108, 35, @on
exec sp_trace_setevent @TraceID, 108, 38, @on
exec sp_trace_setevent @TraceID, 108, 40, @on
exec sp_trace_setevent @TraceID, 109, 1, @on
exec sp_trace_setevent @TraceID, 109, 3, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 7, @on
exec sp_trace_setevent @TraceID, 109, 8, @on
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 109, 19, @on
exec sp_trace_setevent @TraceID, 109, 23, @on
exec sp_trace_setevent @TraceID, 109, 26, @on
exec sp_trace_setevent @TraceID, 109, 35, @on
exec sp_trace_setevent @TraceID, 109, 38, @on
exec sp_trace_setevent @TraceID, 109, 40, @on
exec sp_trace_setevent @TraceID, 110, 1, @on
exec sp_trace_setevent @TraceID, 110, 3, @on
exec sp_trace_setevent @TraceID, 110, 6, @on
exec sp_trace_setevent @TraceID, 110, 7, @on
exec sp_trace_setevent @TraceID, 110, 8, @on
exec sp_trace_setevent @TraceID, 110, 9, @on
exec sp_trace_setevent @TraceID, 110, 10, @on
exec sp_trace_setevent @TraceID, 110, 11, @on
exec sp_trace_setevent @TraceID, 110, 12, @on
exec sp_trace_setevent @TraceID, 110, 14, @on
exec sp_trace_setevent @TraceID, 110, 19, @on
exec sp_trace_setevent @TraceID, 110, 23, @on
exec sp_trace_setevent @TraceID, 110, 26, @on
exec sp_trace_setevent @TraceID, 110, 35, @on
exec sp_trace_setevent @TraceID, 110, 38, @on
exec sp_trace_setevent @TraceID, 110, 40, @on
exec sp_trace_setevent @TraceID, 111, 1, @on
exec sp_trace_setevent @TraceID, 111, 3, @on
exec sp_trace_setevent @TraceID, 111, 6, @on
exec sp_trace_setevent @TraceID, 111, 7, @on
exec sp_trace_setevent @TraceID, 111, 8, @on
exec sp_trace_setevent @TraceID, 111, 9, @on
exec sp_trace_setevent @TraceID, 111, 10, @on
exec sp_trace_setevent @TraceID, 111, 11, @on
exec sp_trace_setevent @TraceID, 111, 12, @on
exec sp_trace_setevent @TraceID, 111, 14, @on
exec sp_trace_setevent @TraceID, 111, 19, @on
exec sp_trace_setevent @TraceID, 111, 23, @on
exec sp_trace_setevent @TraceID, 111, 26, @on
exec sp_trace_setevent @TraceID, 111, 35, @on
exec sp_trace_setevent @TraceID, 111, 38, @on
exec sp_trace_setevent @TraceID, 111, 40, @on
exec sp_trace_setevent @TraceID, 112, 1, @on
exec sp_trace_setevent @TraceID, 112, 3, @on
exec sp_trace_setevent @TraceID, 112, 6, @on
exec sp_trace_setevent @TraceID, 112, 7, @on
exec sp_trace_setevent @TraceID, 112, 8, @on
exec sp_trace_setevent @TraceID, 112, 9, @on
exec sp_trace_setevent @TraceID, 112, 10, @on
exec sp_trace_setevent @TraceID, 112, 11, @on
exec sp_trace_setevent @TraceID, 112, 12, @on
exec sp_trace_setevent @TraceID, 112, 14, @on
exec sp_trace_setevent @TraceID, 112, 19, @on
exec sp_trace_setevent @TraceID, 112, 23, @on
exec sp_trace_setevent @TraceID, 112, 26, @on
exec sp_trace_setevent @TraceID, 112, 35, @on
exec sp_trace_setevent @TraceID, 112, 38, @on
exec sp_trace_setevent @TraceID, 112, 40, @on


-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint

exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'


-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

-- display trace id for future references
select TraceID=@TraceID
goto finish

error: 
select ErrorCode=@rc

finish: 
go


MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site