Views: 2.989 views
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. .
| 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
| Parameter | Description |
|---|---|
| @Ds_User | User (SQL or Windows AD) to be removed from instance |
| @Ds_Usuario_Subitituto | If the user to be removed owns one or more databases or jobs, @Ds_Usuario_Subitituto becomes the owner of these objects. |
| @Fl_Run | BIT 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
| 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.
