SQL Server - How to delete a user from an instance

Hey guys,
All good?

In this post I will demonstrate how to delete a user from a SQL Server instance, which involves deleting users from all databases and then deleting the user's login to the instance. If the user is the owner of a database, change the owner of that database(s) and if the user is the owner of a job, change the job owner as well.

I ended up generating this script because I have seen many instances with several deactivated users who have already been removed/disconnected from their respective companies, but the DBAs end up not removing the user and just deactivating it, which ends up generating an excess of users created in the databases/instance over time.

Script source code

With the source code below, you can automate this process, which often ends up being quite onerous when your instance has several databases and jobs, and you would need to analyze them one by one to check whether a certain user is created there or not.

IF (OBJECT_ID('dbo.stpApaga_Usuario_Instancia') IS NULL) EXEC('CREATE PROCEDURE stpApaga_Usuario_Instancia AS SELECT 1')
GO

ALTER PROCEDURE dbo.stpApaga_Usuario_Instancia (
    @Ds_Usuario VARCHAR(128),
    @Ds_Usuario_Substituto VARCHAR(128),
    @Fl_Executa BIT = 0
)
AS
BEGIN


    DECLARE
        @cmd VARCHAR(MAX) = '',
        @Query VARCHAR(MAX)


    ------------------------------------------------------------------------------------
    -- APAGA USUÁRIOS DO BANCO E SCHEMAS
    ------------------------------------------------------------------------------------

    IF (OBJECT_ID('tempdb..#Apaga_Usuario_Instancia') IS NOT NULL) DROP TABLE #Apaga_Usuario_Instancia
    CREATE TABLE #Apaga_Usuario_Instancia (
        [database] VARCHAR(128) NOT NULL,
        [username] VARCHAR(128) NOT NULL
    )


    SET @Query = '
    USE [?];

    SELECT 
        DB_NAME() AS [database],
        name AS [username]
    FROM
        sys.database_principals WITH(NOLOCK)
    WHERE
        name = ''' + @Ds_Usuario + '''
    '


    INSERT INTO #Apaga_Usuario_Instancia
    EXEC master.dbo.sp_MSforeachdb @Query
    

    SELECT
        @cmd += 'USE [' + [database] + ']; IF (SCHEMA_ID(''' + @Ds_Usuario + ''') IS NOT NULL) DROP SCHEMA [' + @Ds_Usuario + ']; DROP USER ' + QUOTENAME(username) + '; '
    FROM 
        #Apaga_Usuario_Instancia
    ORDER BY
        [database]


    ------------------------------------------------------------------------------------
    -- TROCA DE OWNER
    ------------------------------------------------------------------------------------

    IF (OBJECT_ID('tempdb..#Change_Owner') IS NOT NULL) DROP TABLE #Change_Owner
    SELECT 
        A.name AS [database],
        B.name AS [database_owner]
    INTO
        #Change_Owner
    FROM 
        sys.databases A WITH(NOLOCK)
        JOIN sys.server_principals B WITH(NOLOCK) ON A.owner_sid = B.[sid]
    WHERE
        B.name = @Ds_Usuario

    
    SELECT
        @cmd += 'USE [' + [database] + ']; EXEC sp_changedbowner ''' + @Ds_Usuario_Substituto + '''; '
    FROM 
        #Change_Owner
    ORDER BY
        [database]


    ------------------------------------------------------------------------------------
    -- MUDA O OWNER DOS JOBS
    ------------------------------------------------------------------------------------


    DECLARE
        @sidNovoUsuario VARBINARY(85) = (SELECT sid FROM sys.server_principals WITH(NOLOCK) WHERE name = @Ds_Usuario_Substituto)


    IF (OBJECT_ID('tempdb..#Change_Owner_Job') IS NOT NULL) DROP TABLE #Change_Owner_Job
    SELECT 
        A.name AS [job_name],
        B.name AS [job_owner]
    INTO
        #Change_Owner_Job
    FROM 
        msdb.dbo.sysjobs A WITH(NOLOCK)
        JOIN sys.server_principals B WITH(NOLOCK) ON A.owner_sid = B.[sid] 
    WHERE 
        B.name = @Ds_Usuario


    IF (@Fl_Executa = 1)
    BEGIN

        IF ((SELECT COUNT(*) FROM #Change_Owner_Job) > 0)
        BEGIN

            UPDATE A
            SET
                A.owner_sid = @sidNovoUsuario
            FROM 
                msdb.dbo.sysjobs A
                JOIN sys.server_principals B WITH(NOLOCK) ON A.owner_sid = B.[sid]
            WHERE
                B.name = @Ds_Usuario

        END

    END
    

    ------------------------------------------------------------------------------------
    -- APAGA O LOGIN DA INSTÂNCIA
    ------------------------------------------------------------------------------------


    SET @cmd += 'USE [master]; DROP LOGIN [' + @Ds_Usuario + '];'


    SELECT * FROM #Change_Owner_Job
    SELECT * FROM #Change_Owner
    SELECT * FROM #Apaga_Usuario_Instancia


    PRINT(@cmd)

    IF (@Fl_Executa = 1)
    BEGIN
        EXEC(@cmd)
    END

END

Parameters

Usage examples

EXEC dbo.stpApaga_Usuario_Instancia
    @Ds_Usuario = 'Teste', -- varchar(128)
    @Ds_Usuario_Substituto = 'Usuario_Substituto', -- varchar(128)
    @Fl_Executa = 0 -- bit

Result:

That's it, folks!
A hug and see you next time.