Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.436 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • February 21rd, 2017
  • 0
  • Database DMVs and Catalog Views Maintenance SQL Server

SQL Server - How to delete a user from an instance

Views: 1.473
Reading Time: 2 minutes

Hello people,
Alright?

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 owns any database, change the owner of that database (s) and if the user owns a job, change the owner of the job as well.

I ended up generating this script because I have seen many instances with multiple disabled users that have already been pushed away from their respective companies, but the DBAs end up not removing the user and only deactivating it, which ends up generating too many users created in the databases / instance over time.

Script Source Code

With the source code below, you can automate this process, which often turns out to be very costly when your instance has multiple databases and jobs, and you would need to go over one by one to see if a particular user is created there or not. .

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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')
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

ParameterDescription
@Ds_UserUser (SQL or Windows AD) to be removed from instance
@Ds_Usuario_SubititutoIf the user to be removed owns one or more databases or jobs, @Ds_Usuario_Subitituto becomes the owner of these objects.
@Fl_RunBIT type flag (0 or 1) that defines whether the script will be executed effectively on the instance or not. If you only want to view databases and jobs where @Ds_Usuario is the owner or is simply created, use the value 0.

Examples of use

Transact-SQL
1
2
3
4
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.

tags: sqlsql server

You may also like ...

  • SQL Server - How to find out how long the instance has been online or when the instance was started

  • SQL Server 2017 - How to Pause Rebuild an Index Using Resumable Online Index Rebuilds

  • Importing CSV Files into the SQL Server Database

  • Next SQL Server - How to back up all SQL Agent jobs via command line (CLR C # or Powershell)
  • Previous SQL Server - How to copy / replicate a user's permissions

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.