Posted by Erland Sommarskog on 11/03/05 01:27

Raziq Shekha (raziq_shekha@anadarko.com) writes:
> Does SQL Sever 2000 keep track of all the logins/userids that were
> deleted over the last year? For example, If I deleted a login "Joe"
> from a SQL 2000 server, (and of course the corresponding userid "Joe"
> got deleted from the database) would that be recorded somewhere in the
> system or the production database? i.e. the information such as the
> login "joe" deleted on such and such date from such and such database?

The only place for this is in the transaction log. So if you have your
master in full recovery - and simple is the default - and you never have
truncated the transaction log, you have the information about the login.
If you then get a log reader - see www.lumigent.com or www.logpi.com -
you can get hold of the information.

Same applies to the production database, except that in case I am quite
sure that you have truncated the log. But of course, if you have all
full backups and log backups since last year saved... :-)

It is possible to set up SQL Server to audit such information with the
C2 setting and a trace. But that has to be thought of in advance.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]