Skip to content

Instantly share code, notes, and snippets.

@LetsGoRafting

LetsGoRafting/find invalid active directory logins

Created December 13, 2022 00:47
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save LetsGoRafting/a20b9327763513a3e9b669b334528d25 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save LetsGoRafting/a20b9327763513a3e9b669b334528d25 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. LetsGoRafting created this gist Dec 13, 2022.
    45 changes: 45 additions & 0 deletions find invalid active directory logins
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,45 @@
    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