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.