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

Post Views 3,358 views

Neste artigo

Reading time 2 minutes

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

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

ALTER PROCEDURE dbo.stpApaga_Usuario_Instancia (

@Ds_Usuario VARCHAR(128),

@Ds_Usuario_Substituto VARCHAR(128),

@Fl_Executa BIT = 0

)

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

------------------------------------------------------------------------------------

-- 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

Parâmetros

Parâmetro	Descrição
@Ds_Usuario	Usuário (SQL ou Windows AD) que será removido da instâcia
@Ds_Usuario_Substituto	Caso o usuário que será removido seja owner de um ou mais databases ou jobs, o @Ds_Usuario_Substituto passará a ser o owner desses objetos
@Fl_Executa	Flag do tipo BIT (0 ou 1) que define se o script irá ser executado efetivamente na instância ou não. Caso você queira apenas visualizar os databases e jobs onde o @Ds_Usuario seja o owner ou esteja simplesmente criado, utilize o valor 0.

Exemplos de uso

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

EXEC dbo.stpApaga_Usuario_Instancia

@Ds_Usuario = 'Teste', -- varchar(128)

@Ds_Usuario_Substituto = 'Usuario_Substituto', -- varchar(128)

@Fl_Executa = 0 -- bit

Result:

And that's it, folks!
Um abraço e até a próxima.

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

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

Código-fonte do Script

Parâmetros

Exemplos de uso

You may also like...

Deixe uma respostaCancelar resposta

Código-fonte do Script

Parâmetros

Exemplos de uso

You may also like...

SQL Server – How to learn from basics to certification (Courses, Virtual Labs, Virtual Academy)

SQL Server 2012 – Como criar paginação de dados nos resultados de uma consulta com OFFSET e FETCH

SQL Server – Como utilizar o EXECUTE AS para executar comandos como outro usuário (Impersonate login e user)

Deixe uma respostaCancelar resposta