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 19rd, 2017
  • 6
  • Audit Database DMVs and Catalog Views Maintenance Safety SQL Server

SQL Server - How to copy / replicate a user's permissions

Views: 3.074
Reading Time: 7 minutes

Hello people,

In this post I will demonstrate a script that I developed that has been very useful in my daily life and has the function of copying permissions from one user to another, with the option of just generating the SQL script or actually copying the permissions, which they can be at the level of a specific database or instance.

If you just want to view the permissions of one or more users and / or generate an SQL script with that, see if the article Checking a user's permissions in SQL Server can help you.

To meet this need, I created a Stored Procedure in T-SQL that has the following parameters:

Parameter NameDescription
@User_OriginUser who will have read and copied access
@User_DestinationUser who will receive the access to copy
@Data baseName of the database from which the hits will be copied. If not entered (NULL or ''), all databases will be read.
@Fl_Remove_PermissionsIf this parameter has value = 1, the permissions of the target user are REMOVED so that they are the same as the source user (Will only be executed if @Fl_Execut = 1 too)
@Fl_Cria_UsuariosIf this parameter has value = 1, the script will scan the instance databases and check which ones @Username_Destination exists and @Username_Destination will create @Username_Destination users in those databases.
@Fl_DisplaysResultsIf this parameter has value = 1, the procedure returns multiple queries detailing the current permissions of the @Usuario_Origem and @Usuario_Destino users
@Fl_RunIf this parameter has value = 1, all generated commands will be executed on the instance. If the value is = 0, a PRINT is displayed at the end of execution with the commands.

Stored Procedure Source Code:

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
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
IF (OBJECT_ID('dbo.stpCopia_Permissoes') IS NULL) EXEC('CREATE PROCEDURE dbo.stpCopia_Permissoes AS SELECT 1')
GO
 
ALTER PROCEDURE dbo.stpCopia_Permissoes (
    @Usuario_Origem VARCHAR(MAX),
    @Usuario_Destino VARCHAR(MAX),
    @Database VARCHAR(MAX) = '',
    @Fl_Remover_Permissoes BIT = 0,
    @Fl_Cria_Usuarios BIT = 1,
    @Fl_Exibe_Resultados BIT = 0,
    @Fl_Executar BIT = 0
)
AS BEGIN
 
 
    SET NOCOUNT ON
 
 
    ---------------------------------------------------------------------------------------
    -- CRIAÇÃO DE TABELAS
    ---------------------------------------------------------------------------------------
 
    IF (OBJECT_ID('tempdb..#Permissoes_Database') IS NOT NULL) DROP TABLE #Permissoes_Database
    CREATE TABLE [dbo].[#Permissoes_Database] (
        [database] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [username] [sys].[sysname] NOT NULL,
        [schema] [sys].[sysname] NULL,
        [object] [sys].[sysname] NULL,
        [cmd_state] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [permission_name] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [grant_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [revoke_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
    )
 
 
    IF (OBJECT_ID('tempdb..#Permissoes_Roles') IS NOT NULL) DROP TABLE #Permissoes_Roles
    CREATE TABLE [dbo].[#Permissoes_Roles] (
        [database] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [username] [sys].[sysname] NOT NULL,
        [login_type] [sys].[sysname] NULL,
        [role] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [grant_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [revoke_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
    )
 
 
    IF (OBJECT_ID('tempdb..#Permissoes_Servidor') IS NOT NULL) DROP TABLE #Permissoes_Servidor
    CREATE TABLE [dbo].[#Permissoes_Servidor] (
        [username] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [type_desc] [sys].[sysname] NOT NULL,
        [is_disabled] BIT NOT NULL,
        [class_desc] NVARCHAR(40) NOT NULL,
        [type] NVARCHAR(40) NOT NULL,
        [permission_name] NVARCHAR(50) NOT NULL,
        [state_desc] NVARCHAR(20) NOT NULL,
        [grant_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [revoke_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
    )
 
 
    IF (OBJECT_ID('tempdb..#Permissoes_Roles_Servidor') IS NOT NULL) DROP TABLE #Permissoes_Roles_Servidor
    CREATE TABLE [dbo].[#Permissoes_Roles_Servidor] (
        [username] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [type_desc] [sys].[sysname] NOT NULL,
        [is_disabled] BIT NOT NULL,
        [role] [sys].[sysname] NOT NULL,
        [grant_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [revoke_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
    )
 
 
    IF (OBJECT_ID('tempdb..#Cria_Usuarios') IS NOT NULL) DROP TABLE #Cria_Usuarios
    CREATE TABLE [dbo].[#Cria_Usuarios] (
        [database] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [username] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [type_desc] [sys].[sysname] NOT NULL,
        [default_schema_name] [nvarchar] (128) NULL,
        [authentication_type_desc] [nvarchar] (128) NULL,
        [grant_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
        [revoke_command] [nvarchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
    )
 
 
    DECLARE
        @Query_Permissao_Database VARCHAR(MAX) = '
    SELECT
        DB_NAME() AS [database],
        E.[name] AS [username],
        D.[name] AS [Schema],
        C.[name] AS [Object],
        (CASE WHEN A.state_desc = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE A.state_desc END) AS cmd_state,
        A.[permission_name],
        (CASE
            WHEN C.[name] IS NULL THEN ''USE ['' + DB_NAME() + '']; '' + (CASE WHEN A.state_desc = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE A.state_desc END) + '' '' + A.[permission_name] + '' TO ['' + E.[name] + ''];''
            ELSE ''USE ['' + DB_NAME() + '']; '' + (CASE WHEN A.state_desc = ''GRANT_WITH_GRANT_OPTION'' THEN ''GRANT'' ELSE A.state_desc END) + '' '' + A.[permission_name] + '' ON ['' + DB_NAME() + ''].['' + d.[name] + ''].['' + c.[name] + ''] TO ['' + E.[name] + ''];''
        END) COLLATE DATABASE_DEFAULT AS GrantCommand,
        (CASE
            WHEN C.[name] IS NULL THEN ''USE ['' + DB_NAME() + '']; '' + ''REVOKE '' + A.[permission_name] + '' FROM ['' + E.[name] + ''];''
            ELSE ''USE ['' + DB_NAME() + '']; '' + ''REVOKE '' + A.[permission_name] + '' ON ['' + DB_NAME() + ''].['' + d.[name] + ''].['' + c.[name] + ''] FROM ['' + E.[name] + ''];''
        END) COLLATE DATABASE_DEFAULT AS RevokeCommand
    FROM
        sys.database_permissions                            A   WITH(NOLOCK)
        LEFT JOIN sys.schemas                               B   WITH(NOLOCK) ON A.major_id = B.[schema_id]
        LEFT JOIN sys.all_objects                           C   WITH(NOLOCK)
        JOIN sys.schemas                                    D   WITH(NOLOCK) ON C.[schema_id] = D.[schema_id] ON A.major_id = C.[object_id]
        JOIN sys.database_principals                        E   WITH(NOLOCK) ON A.grantee_principal_id = E.principal_id
    WHERE
        E.[name] IN (''' + @Usuario_Origem + ''', ''' + @Usuario_Destino + ''')'
 
 
 
    DECLARE @Query_Permissoes_Roles VARCHAR(MAX) = '
    SELECT
        DB_NAME() AS [database],
        A.[name] AS [username],
        A.[type_desc] AS LoginType,
        C.[name] AS [role],
        ''EXEC ['' + DB_NAME() + ''].sys.sp_addrolemember '''''' + C.[name] + '''''', '''''' + a.[name] + '''''';'' AS GrantCommand,
        ''EXEC ['' + DB_NAME() + ''].sys.sp_droprolemember '''''' + C.[name] + '''''', '''''' + a.[name] + '''''';'' AS RevokeCommand
    FROM
        sys.database_principals             A   WITH(NOLOCK)
        JOIN sys.database_role_members      B   WITH(NOLOCK) ON A.principal_id = B.member_principal_id
        JOIN sys.database_principals        C   WITH(NOLOCK) ON B.role_principal_id = C.principal_id
    WHERE
        A.[name] IN (''' + @Usuario_Origem + ''', ''' + @Usuario_Destino + ''')'
 
    
 
    IF (NULLIF(LTRIM(RTRIM(@Database)), '') IS NULL)
    BEGIN
 
    
        DECLARE @Query_Alterada VARCHAR(MAX)
 
        ---------------------------------------------------------------------------------------
        -- PERMISSÕES DE TODOS OS DATABASES
        ---------------------------------------------------------------------------------------
 
        SET @Query_Alterada = '
    USE [?];
    ' + @Query_Permissao_Database
    
    
        INSERT INTO #Permissoes_Database
        EXEC master.dbo.sp_MSforeachdb @Query_Alterada
 
 
        ---------------------------------------------------------------------------------------
        -- PERMISSÕES EM ROLES DE TODOS OS DATABASES
        ---------------------------------------------------------------------------------------
 
        SET @Query_Alterada = '
    USE [?];
    ' + @Query_Permissoes_Roles
 
 
        INSERT INTO #Permissoes_Roles
        EXEC master.dbo.sp_MSforeachdb @Query_Alterada
 
 
        ---------------------------------------------------------------------------------------
        -- PERMISSÕES NA INSTÂNCIA
        ---------------------------------------------------------------------------------------
 
        INSERT INTO #Permissoes_Servidor
        SELECT
            A.[name],
            A.[type_desc],
            A.is_disabled,
            B.class_desc,
            B.[type],
            B.[permission_name],
            B.state_desc,
            'USE [master]; ' + B.state_desc + ' ' + B.[permission_name] + ' TO [' + A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI + '];' AS GrantCommand,
            'USE [master]; REVOKE ' + B.[permission_name] + ' FROM [' + A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI + '];' AS RevokeCommand
        FROM
            sys.server_principals               A   WITH(NOLOCK)
            JOIN sys.server_permissions         B   WITH(NOLOCK)    ON  A.principal_id = B.grantee_principal_id
        WHERE
            A.[name] IN (@Usuario_Origem, @Usuario_Destino)
 
    
        ---------------------------------------------------------------------------------------
        -- PERMISSÕES EM SERVER ROLES INSTÂNCIA
        ---------------------------------------------------------------------------------------
 
        INSERT INTO #Permissoes_Roles_Servidor
        SELECT
            A.[name] AS username,
            A.[type_desc],
            A.is_disabled,
            C.[name] AS [role],
            'EXEC [master].[dbo].sp_addsrvrolemember ''' + A.[name] + ''', ''' + C.[name] + ''';' AS GrantCommand,
            'EXEC [master].[dbo].sp_dropsrvrolemember ''' + A.[name] + ''', ''' + C.[name] + ''';' AS RevokeCommand
        FROM
            sys.server_principals               A   WITH(NOLOCK)
            JOIN sys.server_role_members        B   WITH(NOLOCK)    ON  A.principal_id = B.member_principal_id
            JOIN sys.server_principals          C   WITH(NOLOCK)    ON  B.role_principal_id = C.principal_id
        WHERE
            A.[name] IN (@Usuario_Origem, @Usuario_Destino)
 
 
    END
    ELSE BEGIN
    
 
        ---------------------------------------------------------------------------------------
        -- PERMISSÕES DE UM DATABASE
        ---------------------------------------------------------------------------------------
 
        SET @Query_Permissao_Database = '
        USE [' + @Database + ']; ' + @Query_Permissao_Database
 
        INSERT INTO #Permissoes_Database
        EXEC(@Query_Permissao_Database)
 
 
        ---------------------------------------------------------------------------------------
        -- PERMISSÕES EM ROLES DE UM DATABASE
        ---------------------------------------------------------------------------------------
 
        SET @Query_Permissoes_Roles = '
        USE [' + @Database + ']; ' + @Query_Permissoes_Roles
 
        INSERT INTO #Permissoes_Roles
        EXEC(@Query_Permissoes_Roles)
 
 
    END
 
    
    ---------------------------------------------------------------------------------------
    -- CRIA OS USUÁRIOS (CASO NÃO EXISTAM)
    ---------------------------------------------------------------------------------------
 
    DECLARE @Comando VARCHAR(MAX) = ''
 
    IF (@Fl_Cria_Usuarios = 1)
    BEGIN
    
 
        DECLARE @Query_Cria_Usuarios VARCHAR(MAX) = '
    USE [?];
 
    SELECT
        DB_NAME() AS [database],
        A.[name] AS username,
        A.[type_desc],
        A.default_schema_name,
        A.authentication_type_desc,
        ''USE ['' + DB_NAME() + '']; CREATE USER ['' + A.[name] + ''] FOR LOGIN ['' + A.[name] + ''] WITH DEFAULT_SCHEMA=['' + ISNULL(a.default_schema_name, ''dbo'') + ''];'' AS GrantCommand,
        ''USE ['' + DB_NAME() + '']; DROP USER ['' + A.[name] + ''];'' AS RevokeCommand
    FROM
        sys.database_principals A WITH(NOLOCK)
    WHERE
        A.[name] = ''' + @Usuario_Origem + '''
        AND NOT EXISTS(SELECT NULL FROM sys.database_principals WITH(NOLOCK) WHERE [name] = ''' + @Usuario_Destino + ''')'
 
    
        INSERT INTO #Cria_Usuarios
        EXEC master.dbo.sp_MSforeachdb @Query_Cria_Usuarios
 
 
        DELETE FROM #Cria_Usuarios
        WHERE [database] != @Database
 
 
        SELECT
            @Comando += REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino)
        FROM
            #Cria_Usuarios
        WHERE
            username = @Usuario_Origem
 
 
 
        IF (@Fl_Executar = 1)
            EXEC(@Comando)
        ELSE BEGIN
            PRINT '-- Criação de usuários'
            PRINT @Comando
            PRINT ''
        END
 
 
    END
 
    ---------------------------------------------------------------------------------------
    -- EXECUTA AS PERMISSÕES
    ---------------------------------------------------------------------------------------
 
 
    SET @Comando = ''
 
    IF (@Fl_Remover_Permissoes = 1)
    BEGIN
    
        SELECT
            @Comando += revoke_command
        FROM
            #Permissoes_Database
        WHERE
            username = @Usuario_Destino
 
    END
 
 
    SELECT
        @Comando += REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino)
    FROM
        #Permissoes_Database
    WHERE
        username = @Usuario_Origem
 
 
 
    IF (@Fl_Executar = 1)
        EXEC(@Comando)
    ELSE BEGIN
        PRINT '-- Permissões de Database'
        PRINT @Comando
        PRINT ''
    END
 
 
 
 
    SET @Comando = ''
 
    IF (@Fl_Remover_Permissoes = 1)
    BEGIN
    
        SELECT
            @Comando += revoke_command
        FROM
            #Permissoes_Roles
        WHERE
            username = @Usuario_Destino
 
    END
 
    SELECT
        @Comando += REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino)
    FROM
        #Permissoes_Roles
    WHERE
        username = @Usuario_Origem
 
 
 
    IF (@Fl_Executar = 1)
        EXEC(@Comando)
    ELSE BEGIN
        PRINT '-- Permissões em Roles de Databases'
        PRINT @Comando
        PRINT ''
    END
 
 
 
 
    IF (NULLIF(LTRIM(RTRIM(@Database)), '') IS NULL)
    BEGIN
        
 
        SET @Comando = ''
 
        IF (@Fl_Remover_Permissoes = 1)
        BEGIN
    
            SELECT
                @Comando += revoke_command
            FROM
                #Permissoes_Roles_Servidor
            WHERE
                username = @Usuario_Destino
 
        END
 
        SELECT
            @Comando += REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino)
        FROM
            #Permissoes_Roles_Servidor
        WHERE
            username = @Usuario_Origem
 
 
 
 
        IF (@Fl_Executar = 1)
            EXEC(@Comando)
        ELSE BEGIN
            PRINT '-- Permissões em roles da instância'
            PRINT @Comando
            PRINT ''
        END
 
 
        SET @Comando = ''
 
        IF (@Fl_Remover_Permissoes = 1)
        BEGIN
    
            SELECT
                @Comando += revoke_command
            FROM
                #Permissoes_Servidor
            WHERE
                username = @Usuario_Destino
 
        END
 
 
        SELECT
            @Comando += REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino)
        FROM
            #Permissoes_Servidor
        WHERE
            username = @Usuario_Origem
 
 
 
        IF (@Fl_Executar = 1)
            EXEC(@Comando)
        ELSE BEGIN
            PRINT '-- Permissões na instância'
            PRINT @Comando
            PRINT ''
        END
 
    END
 
 
 
    IF (@Fl_Exibe_Resultados = 1)
    BEGIN
        
 
        SELECT
            [database],
            username,
            [schema],
            [object],
            cmd_state,
            [permission_name],
            REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino) AS grant_command,
            REPLACE(revoke_command, @Usuario_Origem, @Usuario_Destino) AS revoke_command
        FROM
            #Permissoes_Database
        WHERE
            username = @Usuario_Origem
 
 
        SELECT
            [database],
            username,
            [login_type],
            [role],
            REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino) AS grant_command,
            REPLACE(revoke_command, @Usuario_Origem, @Usuario_Destino) AS revoke_command
        FROM
            #Permissoes_Roles
        WHERE
            username = @Usuario_Origem
 
 
        IF (NULLIF(LTRIM(RTRIM(@Database)), '') IS NULL)
        BEGIN
 
            SELECT
                username,
                [type_desc],
                is_disabled,
                class_desc,
                [type],
                [permission_name],
                state_desc,
                REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino) AS grant_command,
                REPLACE(revoke_command, @Usuario_Origem, @Usuario_Destino) AS revoke_command
            FROM
                #Permissoes_Servidor
            WHERE
                username = @Usuario_Origem
 
 
            SELECT
                username,
                [type_desc],
                is_disabled,
                [role],
                REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino) AS grant_command,
                REPLACE(revoke_command, @Usuario_Origem, @Usuario_Destino) AS revoke_command
            FROM
                #Permissoes_Roles_Servidor
            WHERE
                username = @Usuario_Origem
 
 
        END
 
 
        IF (@Fl_Cria_Usuarios = 1)
        BEGIN
 
            SELECT
                [database],
                username,
                [type_desc],
                default_schema_name,
                authentication_type_desc,
                REPLACE(grant_command, @Usuario_Origem, @Usuario_Destino) AS grant_command,
                REPLACE(revoke_command, @Usuario_Origem, @Usuario_Destino) AS revoke_command
            FROM
                #Cria_Usuarios
            WHERE
                username = @Usuario_Origem
 
        END
 
 
    END
 
 
END

1 Example

In this example, I will demonstrate how to generate the script to copy the permissions of the user "Usuario_Teste" to the user "Test" in the "master" database.

Transact-SQL
1
2
3
4
5
6
7
8
EXEC master.dbo.stpCopia_Permissoes
    @Usuario_Origem = 'Usuario_Teste', -- varchar(max)
    @Usuario_Destino = 'Teste', -- varchar(max)
    @Database = 'master', -- varchar(max)
    @Fl_Remover_Permissoes = 1, -- bit
    @Fl_Cria_Usuarios = 1, -- bit
    @Fl_Exibe_Resultados = 1, -- bit
    @Fl_Executar = 0 -- bit

Result:

2 Example

In this example, I will demonstrate how to generate the script to copy the permissions of the user “Usuario_Teste” to the user “Test” in all databases of the instance. When the @Database parameter is not informed, the Stored Procedure will iterate between all databases and still generate permissions at the server level and server roles.

Transact-SQL
1
2
3
4
5
6
7
8
EXEC master.dbo.stpCopia_Permissoes
    @Usuario_Origem = 'Usuario_Teste', -- varchar(max)
    @Usuario_Destino = 'Teste', -- varchar(max)
    @Database = NULL, -- varchar(max)
    @Fl_Remover_Permissoes = 1, -- bit
    @Fl_Cria_Usuarios = 1, -- bit
    @Fl_Exibe_Resultados = 1, -- bit
    @Fl_Executar = 0 -- bit

Result:

3 Example

In this example, I will demonstrate using the @Fl_Executar = 1 parameter, where actually changes are made to the instance by the Stored Procedure and permissions are actually copied from @Usuario_Origem to @Usuario_Destino.

Transact-SQL
1
2
3
4
5
6
7
8
EXEC master.dbo.stpCopia_Permissoes
    @Usuario_Origem = 'Usuario_Teste', -- varchar(max)
    @Usuario_Destino = 'Teste', -- varchar(max)
    @Database = NULL, -- varchar(max)
    @Fl_Remover_Permissoes = 1, -- bit
    @Fl_Cria_Usuarios = 1, -- bit
    @Fl_Exibe_Resultados = 1, -- bit
    @Fl_Executar = 1 -- bit

Result:
In this case, I ran the SP again to bring the updated permissions of the user “Teste”, who now has the same permissions as the user “Usuario_Teste”

That's it, guys.
I hope you enjoyed this post.

A hug and see you next.

tags: auditingpermissionsecuritysqlsql server

You may also like ...

  • Analysis Services - How to use XLMA or Powershell to process cubes and dimensions via command line (T-SQL) or SQL Agent Job

  • How to convert milliseconds, seconds, or minutes to TIME in SQL Server

  • SQL Server - Why Not Use SET ANSI_WARNINGS OFF

  • Next SQL Server - How to delete a user from an instance
  • Previous SQL Server 2016 - Using Native JSON Support (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)

Comments

  • Comments6
  • Pingbacks0
  1. dbasergioramos said:
    15 April 2020 07 gies: 51

    Dirceu, Very show and congratulations on sharing these scripts. When I grow up I want to be like you! 🙂

    Reply
  2. Julio Romano (@_jlromano) said:
    13 from 2019 to 16 at 45: XNUMX

    Very good, thanks for sharing.

    Reply
    • Dirceu Resende said:
      18 from 2019 from September to 23: 15

      You're welcome, Julius. Any questions, just talk and I accept suggestions for new articles kkkkkkkkkk

      Reply
  3. Michelle said:
    December 12 from 2018 to 19: 26

    Is this script also for copying users to a mirror instance?

    Reply
  4. Dorival Vasconcelos said:
    27 from 2018 from September to 10: 25

    Very good script, congratulations for sharing knowledge!
    However it did not help me solve my problem.
    I have a user in a test database with restricted permissions (some views, two tables and two procedures), I tried to reproduce this same scenario in another test database with the most updated data and the user is unable to finalize the insert (purpose) of the procedure .Returns an error message “Error during trigger execution” pointing to a trigger on one of these tables that the user already has access to (INSERT, UPDATE, SELECT).
    I used know script to see what the difference of access of this user between a database and another permissions are exactly the same, however does not work in database2.

    I can say that this is permission because if I add the user in role db_owner the procedure runs without problems.

    Reply
  5. Humberto Jacobina said:
    29 from 2018 to 10 at 36: XNUMX

    Very good your script !!! helped me a lot.

    Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.