Force Drop Login

 1
 2------------------------------------
 3-- Drop user - SQL Script         --
 4------------------------------------
 5--
 6-- Login name to delete
 7DECLARE @userToDelete nvarchar(200) = 'TestLogin';
 8--
 9--
10IF (NOT EXISTS (SELECT * FROM sys.syslogins AS L WHERE L.loginname = @userToDelete))
11BEGIN
12    RAISERROR('
13    Login not found.
14    ', 50000, 0) WITH log
15END
16
17DECLARE @sql nvarchar(max);
18
19PRINT '--  CHECK Databases'
20USE [master];
21SET @sql = '';
22select @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';
23PRINT @sql;
24EXEC (@sql);
25
26PRINT '--  CHECK Jobs'
27USE [master];
28SET @sql = '';
29select @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
30PRINT @sql;
31EXEC (@sql);
32
33PRINT '--  CHECK Schemas'
34SET @sql = '
35DECLARE @sql nvarchar(max);
36SET @sql = '''';
37SELECT @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+''')
38IF NOT (@sql='''')
39BEGIN
40    SET @sql = ''USE [?]''+CHAR(13)+@sql+''DROP USER ['+@userToDelete+']''+CHAR(13);
41    PRINT @sql;
42    EXEC (@sql);
43END
44'
45exec sp_MSforeachdb @sql;
46
47PRINT '--  CHECK Connections'
48USE [master];
49SET @sql = '';
50select @sql = @sql + 'KILL ' + cast(spid AS NVARCHAR(10)) + ';'+ char(13) FROM master..sysprocesses Where loginame = @userToDelete;
51PRINT @sql;
52EXEC (@sql);
53
54PRINT '-- DROP Login'
55SET @sql = 'DROP LOGIN ['+@userToDelete+']'+CHAR(13);
56PRINT @sql;
57EXEC (@sql);
58
59GO