Force Drop login

A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped.

This code can be used to drop Login from SQL Server and user name associated with this Login in different databases.

------------------------------------
-- Drop user - SQL Script         --   
------------------------------------
--
-- Login name to delete
DECLARE @userToDelete nvarchar(200) = 'TestLogin';
--
--
IF (NOT EXISTS (SELECT * FROM sys.syslogins AS L WHERE L.loginname = @userToDelete))
BEGIN
    RAISERROR('
    Login not found.
    ', 50000, 0) WITH log
END


DECLARE @sql nvarchar(max);

PRINT '--  CHECK Databases'
USE [master];
SET @sql = '';
select @sql=@sql+'USE ['+d.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;'+CHAR(13) from  sys.sysdatabases d left join master.sys.syslogins l on d.sid = l.sid WHERE l.name = 'TestLogin';
PRINT @sql;
EXEC (@sql);


PRINT '--  CHECK Jobs'
USE [master];
SET @sql = '';
select @sql=@sql+'EXEC msdb.dbo.sp_update_job @job_name=N'''+s.name+''', @owner_login_name=N''sa'';'+CHAR(13) from  msdb..sysjobs s left join master.sys.syslogins l on s.owner_sid = l.sid WHERE l.name = @userToDelete
PRINT @sql;
EXEC (@sql);


PRINT '--  CHECK Schemas'
SET @sql = '
DECLARE @sql nvarchar(max);
SET @sql = '''';
SELECT @sql=@sql+''ALTER AUTHORIZATION ON SCHEMA::['' + s.name + ''] TO [dbo];''+CHAR(13) FROM [?].sys.schemas AS s INNER JOIN [?].sys.sysusers AS u ON s.principal_id = u.uid WHERE (u.name = '''+@userToDelete+''')
IF NOT (@sql='''')
BEGIN
    SET @sql = ''USE [?]''+CHAR(13)+@sql+''DROP USER ['+@userToDelete+']''+CHAR(13);
    PRINT @sql;
    EXEC (@sql);
END
'
exec sp_MSforeachdb @sql;


PRINT '--  CHECK Connections'
USE [master];
SET @sql = '';
select @sql = @sql + 'KILL ' + cast(spid AS NVARCHAR(10)) + ';'+ char(13) FROM master..sysprocesses Where loginame = @userToDelete;
PRINT @sql;
EXEC (@sql);

PRINT '-- DROP Login'
SET @sql = 'DROP LOGIN ['+@userToDelete+']'+CHAR(13);
PRINT @sql;
EXEC (@sql);

GO

 

source: https://msdn.microsoft.com/en-us/library/ms188012.aspx