SQL - check invalid logins in your SQL Server

Today we will learn on how to scan all the logins created in SQL Server that are no longer valid

Run the below SQL query by replacing your SQL Server name:

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

IF OBJECT_ID('tempdb..#invalidlogins2') IS NOT NULL
DROP TABLE #invalidlogins2

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

CREATE TABLE #invalidlogins2(
ServerName Varchar(100)
, NTLOGIN SYSNAME)

INSERT INTO #invalidlogins
EXEC [<enter your server name>].master.sys.sp_validatelogins

if not exists(select * from #invalidlogins2 where NTLOGIN in (select NTLOGIN from #invalidlogins) and servername = '<enter your server name>'  )
begin
  insert into #invalidlogins2
select '<enter your server name>', NTLOGIN from #invalidlogins
end

truncate table #invalidlogins

SELECT * FROM #invalidlogins2 order by 1

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

IF OBJECT_ID('tempdb..#invalidlogins2') IS NOT NULL
DROP TABLE #invalidlogins2