How to delete multiple logins from SQL Server (Sp_DropLogins )

I have been asked several times to delete multiple logins in a day in my environment then initially I was deleting them one after one using sp_droplogin with a single parameter @login . I thought it would have been really great with Microsoft if they have it as sp_droplogins so that I can drop multiple logins by just passing the login ID’s.

However I created a script for it and I would like to share here and save you time. This stored procedure makes use of a user defined function to split the string (Multiple logins) we pass to it.

Step1: Create a function Dbo.split using the code below in the master database context, thanks to coder

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))     
returns @temptable TABLE (items varchar(8000))     
as     
begin     
declare @idx int      
declare @slice varchar(8000)      
select @idx = 1       
if len(@String)<1 or @String is null  
return      while @idx!= 0      
begin       
set @idx = charindex(@Delimiter,@String)       
if @idx!=0        
set @slice = left(@String,@idx - 1)       
else        
set @slice = @String       
if(len(@slice)>0)    
insert into @temptable(Items) 
values(@slice)       
set @String = right(@String,len(@String) - @idx)       
if len(@String) = 0 break      
end 
return     
END

Sample result from the function:

If you pass a string with multiple login ids as ‘test123, test456’

SELECT * FROM dbo.SPLIT(‘test123,test456′,’,’)

Result:

Step 2 : Create a stored procedure with name sp_droplogins, if we feel as it is confusing with system styored procedure sp_droplogin, you can change it your name there is no impact with name on the internal code of the stored procedure.

Create a stored Procedure using the below code in master database context.,

Create procedure sp_droplogins
( 
 @loginIDS Varchar(max)
)
AS
DECLARE @login VARCHAR(max)
DECLARE @Droplogins CURSOR
SET @droplogins = CURSOR FOR
select name from sys.syslogins where name IN
(SELECT* FROM dbo.SPLIT(@loginIds,',')
)
OPEN @droplogins
FETCH NEXT
FROM @droplogins INTO @login
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sys.sp_droplogin @login
FETCH NEXT
FROM @droplogins INTO @login
END
CLOSE @droplogins
DEALLOCATE @droplogins
GO

Step3:

The execution of stored procedure looks like

EXEC sp_droplogins @loginids='test123,test456'

By executing the above stored procedure, the logins with names ‘test123’ and ‘test456’ are dropped or deleted.

Note: By deleting the logins, the associated database users are not deleted.