Auditing SQL 2000 Environment!!!

[DrSql]

I am trying to audit my SQL Server environment. Majority of my users are NT authentication. When an account is diabled or deleted from the Domain the account credetails are still in syslogin tables. I want to find those accounts and remove. Also I want to find out when was the last sucessfull logins by the users. Is there any sys tables that I refer and gather these details or any tools that I can obtain.
Thanks

Dr.Sql
Good Luck.

 

[mrdenny]

The procedure sp_validatelogins can be used to find app the accounts which have been removed from the domain.

I've used this script to handle this. It handles removing the login from all the databases, and will output some info if the users owns objects.

declare @Login sysname
declare @sid varbinary(100)
declare @db sysname
declare @CMD varchar(8000)
create table #vl (SID varbinary(100), login sysname)

insert into #vl
exec sp_validatelogins

declare cur CURSOR for select SID, login from #vl
open cur
fetch next from cur into @sid, @login
while @@FETCH_STATUS = 0
BEGIN
	set @CMD = 'use [?] 
declare @name sysname
if exists (select * from sysusers where sid = convert(varbinary(100), ''' + convert(varchar(100), @sid) + ''')) 
begin 
	select @name = name 
		from sysusers 
		where sid = convert(varbinary(100), ''' + convert(varchar(100), @sid) + ''') 
	if @name = ''dbo''
	BEGIN
		exec sp_changedbowner ''sa''
		set @name = null
		select @name = name 
		from sysusers 
		where sid = convert(varbinary(100), ''' + convert(varchar(100), @sid) + ''') 
	END
	if @name is not null
	BEGIN
		if exists (select * from sysobjects where uid = (select uid from sysusers where name = @name))
		BEGIN
			print @name + '' owns objects in the database '' + db_name() + '' and can not be dropped.''
		END
		ELSE
		BEGIN
			exec sp_dropuser @name print @name + '' removed from '' + db_name() 
		END
	END
end'
	exec sp_MSForEachDB @CMD
	exec sp_revokelogin @loginame=@login
	--print @CMD
	fetch next from cur into @sid, @login
END
close cur
deallocate cur
drop table #vl

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com