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′,’,’)
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
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.