Remove SQL User

Removing a user from a server is never just DROP LOGIN - they may own schemas and objects in a dozen databases. This stored procedure takes a username, walks every online database, and collects everything that references them: tables, procedures and views in their schemas, the schemas themselves, the database users, and finally the matching login. It then PRINTs one clean, ordered drop script - USE/GO/DROP per object - ready to review and run.

USE master;
GO

IF object_id('RemoveUserFromAllDatabases') IS NOT NULL
BEGIN
	DROP PROCEDURE RemoveUserFromAllDatabases
END
GO

CREATE PROCEDURE RemoveUserFromAllDatabases
(
	@UserName VarChar(255)
) AS
BEGIN
	set nocount on;

	create table #users
	(
		dbname varchar(128),
		name varchar(128)
	)

	create table #schemas
	(
		dbname varchar(128),
		name varchar(128)
	)

	create table #objects
	(
		dbname varchar(128),
		name varchar(128),
		type varchar(10)
	)

	declare @dbname varchar(128)
	declare @sql varchar(1000)

	set @UserName = replace(replace(@UserName, ']', ''), '[', '')

	declare dbs cursor for
		select name from sys.databases where state_desc = 'ONLINE'
	open dbs

	fetch from dbs into @dbname
	while(@@fetch_status = 0)
	begin--if
		set @sql = 'insert into #schemas ' +
			'select ''[' + @dbname + ']'', name from [' + @dbname + '].sys.schemas where principal_id IN (select uid from [' + @dbname + ']..sysusers where lower(name) = ''' + lower(@UserName) + ''')'
		exec(@sql)
		
		set @sql = 'insert into #users select ''[' + @dbname + ']'', ''['' + name + '']'' from [' + @dbname + '].dbo.sysusers where lower(name) = ''' + lower(@UserName) + ''''
		exec(@sql)

		set @sql = 'insert into #objects select ''[' + @dbname + ']'', ''['' + (select name from [' + @dbname + '].sys.schemas where schema_id = objects.schema_id) + ''].['' + name + '']'',''
			+ 'type from [' + @dbname + '].sys.objects where schema_id in '
			+ '(select schema_id from [' + @dbname + '].sys.schemas where principal_id IN (select uid from [' + @dbname + ']..sysusers where lower(name) = ''' + lower(@UserName) + '''))'
		exec(@sql)

		fetch from dbs into @dbname
	end--if

	close dbs deallocate dbs

	select
		'USE ' + dbname + ';' + Char(13) + Char(10) +
		'GO' + Char(13) + Char(10) +
		'DROP '
		+
			CASE
				WHEN [type] = 'U' THEN 'TABLE'
				WHEN [type] = 'P' THEN 'PROCEDURE'
				WHEN [type] = 'V' THEN 'VIEW'
			END
		+ ' ' + name + ';' + Char(13) + Char(10) +
		'GO'
	from
		#objects

	union all

	select
		'USE ' + dbname + ';' + Char(13) + Char(10) +
		'GO' + Char(13) + Char(10) +
		'DROP SCHEMA [' + name + '];' + Char(13) + Char(10) +
		'GO'
	from
		#schemas
		
	union all

	select
		'USE ' + dbname + ';' + Char(13) + Char(10) +
		'GO' + Char(13) + Char(10) +
		'DROP USER ' + name + ';' + Char(13) + Char(10) +
		'GO'
	from
		#users
		
	union all

	select
		'DROP LOGIN [' + name + ']' + Char(13) + Char(10) + 'GO'
	from
		syslogins
	where
		lower(name) like '%' + lower(@UserName) + '%'

	drop table #users
	drop table #schemas
	drop table #objects
END
GO