SQL Server - Cómo eliminar un usuario de una instancia

Hola, chicos,
¿Todo está bien?

En esta publicación, demostraré cómo eliminar un usuario de una instancia de SQL Server, lo que implica eliminar usuarios de todas las bases de datos y luego eliminar el inicio de sesión del usuario en la instancia. Si el usuario es el propietario de una base de datos, cambie el propietario de esa base de datos y si el usuario es el propietario de un trabajo, cambie también el propietario del trabajo.

Terminé generando este script porque he visto muchas instancias con varios usuarios desactivados que ya han sido eliminados/desconectados de sus respectivas empresas, pero los DBA terminan no eliminando al usuario y simplemente desactivándolo, lo que termina generando un exceso de usuarios creados en las bases de datos/instancia con el tiempo.

Código fuente del script

Con el código fuente a continuación, puedes automatizar este proceso, que muchas veces termina siendo bastante oneroso cuando tu instancia tiene varias bases de datos y trabajos, y necesitarías analizarlos uno por uno para verificar si un determinado usuario se crea allí o no.

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

Ejemplos de uso

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

Resultado:

¡Eso es todo, amigos!
Un abrazo y hasta la próxima.