SQL Server - Como apagar um usuário de uma instância

Olá pessoal,
Tudo bem?

Neste post vou demonstrar como apagar um usuário de uma instância SQL Server, o que envolve apagar os usuários de todos os databases e depois apagar o login do usuário na instância. Caso o usuário seja owner de algum database, altera o owner desse(s) database(s) e se o usuário for owner de algum job, faz a troca do owner do job também.

Acabei gerando esse script porque tenho visto muitas instâncias com vários usuários desativados que já foram afastados/desligados de suas respectivas empresas, mas os DBAs acabam não removendo o usuário e apenas desativando-o, o que acaba gerando um excesso de usuários criados nos databases/instância ao longo do tempo.

Código-fonte do Script

Com o código-fonte abaixo, vocês podem automatizar esse processo, que muitas vezes, acaba sendo bem oneroso quando a sua instância possui vários databases e jobs, e você precisaria ficar analisando um a um para verificar se um determinado usuário está criado lá ou não.

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

Parâmetros

Exemplos de uso

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

Resultado:

É isso aí, pessoal!
Um abraço e até a próxima.