Remove Logins Script

use master
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--*************************************************************************************************
--*************************************************************************************************
-- Generate a script to remove a user from a SQL Server instance. 
-- The scripot will remove the user from all databases and remove the login from the SQL instance.
-- Any empty schemas owned by the user will be dropped, non-empty schemas will be changed to be
-- owned by 'dbo' using ALTER AUTHORIZATION
--*************************************************************************************************
--*************************************************************************************************

-- remove previous instance of this proc
if exists (select * from sysobjects where id = object_id('[dbo].[sp_RemoveUserScript]') and sysstat & 0xf = 4)
    drop procedure [dbo].sp_RemoveUserScript
go

--*************************************************************************************************
-- Create the stored procedure
-- 
-- Inputs:
-- @name - login name (not database user name) that will be 
--
-- Outputs:
-- List of commands to run are printed to the message out
--*************************************************************************************************
create proc sp_RemoveUserScript @name varchar(75)
as 
begin
    -- suppress count messages
    set nocount on
    
    -- variable to hold commands to be run
    declare @cmd varchar(4000)    

    -- temp table to hold commands
    if (select object_id('tempdb..##commands')) is not null
     drop table ##commands
    create table ##commands (command varchar(max))

    -- create command to deal with schemas, generate either drop or alter auth statements
    -- depending on whether or not the schema has any objects in it. 
    set @cmd = ';with sch as (
            select 
                u.name as username, 
                s.name as schemaname, 
                sum(coalesce(cnt, 0)) as objectcount
            from [?].sys.sysusers u 
            inner join [?].sys.schemas s 
                on s.principal_id = u.uid 
            left outer join (
                select 1 as cnt, schema_id
                from [?].sys.objects
            ) t on s.schema_id = t.schema_id
            where u.name = '''+@name+'''
            group by u.name, s.name
        )
        insert into ##commands 
        select 
            case when (objectcount>0) then ''use ?; ALTER AUTHORIZATION ON SCHEMA::[''+schemaname+''] TO dbo;''
            else ''use ?; drop schema [''+schemaname+''];''
            end 
        from sch'
        
    -- execute schema search for all databases
    exec dbo.sp_MSforeachdb @cmd

    -- create command to generate drop user statements for all databases. uses SIDs to ensure
    -- that all users connected to the login are removed.
    set @cmd = 'insert into ##commands 
        select ''use ?; exec sp_dropuser [''+ d.name +'']; '' 
        from [?].dbo.sysusers d 
        inner join master.dbo.syslogins m 
        on d.sid = m.sid 
        where m.name = ''' + @name + ''''
    
    -- execute the drop user generation commands 
    exec dbo.sp_MSforeachdb @cmd
    
    -- add a final 'drop this login' command
    insert into ##commands select 'use master; exec sp_droplogin [' + @name + '];' from master.sys.syslogins where name = '' + @name + ''
    
    -- now that we have all the commands ready, generate output to message out. this does
    -- require a cursor so that we can print the statements.
    print ''
    print ''
    print ''
    print '------------------ Clip Here ------------------'
    print ''
    declare cmdlist cursor for select command from ##commands
    open cmdlist
    fetch next from cmdlist into @cmd
    while 0 = @@FETCH_STATUS
    begin
        print @cmd
        fetch next from cmdlist into @cmd
    end
    
    -- clean up after ourselves
    close cmdlist
    deallocate cmdlist
    drop table ##commands
end