History of logins or Userids

[Raziq Shekha]

Hello All,

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?

Long Live SOX :)

Thanks,
Raziq.

*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Raziq Shekha (ra**********@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, es****@sommarskog.se

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

[Raziq Shekha]

Are there any third party tools that will do this?

Raziq.
*** Sent via Developersdex http://www.developersdex.com ***

[Erland Sommarskog]

Raziq Shekha (ra**********@anadarko.com) writes:

Are there any third party tools that will do this?

Have a look at Lumigent's Entegra, http://www.lumigent.com. I don't know
if they have what they are asking for, but I would expect it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Raziq Shekha]

Are there any third party tools that will do this?

Raziq.
*** Sent via Developersdex http://www.developersdex.com ***