find invalid active directory logins queries

find invalid active directory logins

declare @user sysname
declare @domain varchar(100)
 
set @domain = 'mydomain'
 
declare recscan cursor for
select name from sys.server_principals
where type = 'U' and name like @domain+'%'
 
open recscan 
fetch next from recscan into @user
 
while @@fetch_status = 0
begin
    begin try
        exec xp_logininfo @user
    end try
    begin catch
        --Error on xproc because login doesn't exist
        print 'drop login '+convert(varchar,@user)
    end catch
 
    fetch next from recscan into @user
end
 
close recscan
deallocate recscan


EXEC sys.sp_validatelogins

IF (OBJECT_ID('tempdb..#invalidlogins') IS NOT NULL)
BEGIN
DROP TABLE #invalidlogins
END

CREATE TABLE #invalidlogins(
 ACCTSID VARBINARY(85)
, NTLOGIN SYSNAME)

INSERT INTO #invalidlogins
EXEC sys.sp_validatelogins

SELECT NTLOGIN FROM #invalidlogins
order by 1