Hey guys,
Are you all right?
In this post I will demonstrate a really cool script that I developed and which aims to generate SQL scripts to backup (reverse engineering) permissions, users, logins and roles of an instance in SQL Server.
This script was designed to be used in instance migrations, whether to a new server or to upgrade the version of your SQL Server instance, in a practical and easy-to-use way. If you also want to migrate SQL Agent jobs to another instance as well, see more by accessing the post SQL Server – How to backup all SQL Agent jobs via command line (CLR C# or Powershell).
Using the Stored Procedure below, it is possible to generate SQL commands to perform the following tasks:
- Script to backup login creation (Windows AD and SQL Server authentication)
- Script to backup instance login permissions (Server level permissions)
- Script to backup login permissions in instance roles (Server roles permissions)
- Script to backup user creation in databases (WITH and WITHOUT login)
- Script to backup database login permissions (Database level permissions)
- Script to backup login permissions in database roles (Database roles permissions)
As prerequisites for using stpExporta_Logins, you will need:
- A user with sysadmin permission to execute the Stored Procedure
- Create the Stored Procedure stpWrite_File_FSO, available in the post File operations using OLE Automation in SQL Server.
The script of stpExporta_Logins, which I will present in this post, was inspired by the Stored Procedure in the post SQL Server - How to copy/replicate a user's permissions, where the procedure in this post aims to generate SQL files to replicate permissions to another environment, while the script in the other post aims to copy permissions from one user to another in the same instance and therefore, does not have several validations and checks, such as creating logins.
Source code of the stpExporta_Logins Procedure
To perform all tasks easily, simply run the source code below and create stpExporta_Logins in a database of your choice.
This Stored Procedure uses SP stpWrite_File_FSO to write the files physically to disk, which requires the resource ‘Ole Automation Procedures’ is enabled on the instance.
However, you don't need to worry about this part because the SP has a check that, if this feature is not activated in the instance, the SP itself will activate it during execution and will deactivate it after the files are generated, keeping this configuration the same as it was before the Procedure was executed.
Source code:
IF (OBJECT_ID('dbo.stpExporta_Logins') IS NULL) EXEC('CREATE PROCEDURE dbo.stpExporta_Logins AS SELECT 1')
GO
ALTER PROCEDURE dbo.stpExporta_Logins (
@Ds_Diretorio_Saida VARCHAR(500),
@Fl_Permissoes_Database BIT = 0,
@Fl_Arquivo_Unico BIT = 0
)
AS BEGIN
SET NOCOUNT ON
DECLARE
@Fl_Ole_Automation_Ativo BIT = (SELECT (CASE WHEN CAST([value] AS VARCHAR(MAX)) = '1' THEN 1 ELSE 0 END) FROM sys.configurations WHERE [name] = 'Ole Automation Procedures')
---------------------------------------------------------------------------------------
-- CRIAÇÃO DE TABELAS
---------------------------------------------------------------------------------------
IF (OBJECT_ID('tempdb..#Permissoes_Database') IS NOT NULL) DROP TABLE #Permissoes_Database
CREATE TABLE [dbo].[#Permissoes_Database] (
[database] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[username] [sys].[sysname] NOT NULL,
[schema] [sys].[sysname] NULL,
[object] [sys].[sysname] NULL,
[cmd_state] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[permission_name] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[grant_command] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[revoke_command] [varchar] (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] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[username] [sys].[sysname] NOT NULL,
[login_type] [sys].[sysname] NULL,
[role] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[grant_command] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[revoke_command] [varchar] (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] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[type_desc] [sys].[sysname] NOT NULL,
[is_disabled] BIT NOT NULL,
[class_desc] varchar(40) NOT NULL,
[type] varchar(40) NOT NULL,
[permission_name] varchar(50) NOT NULL,
[state_desc] varchar(20) NOT NULL,
[grant_command] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[revoke_command] [varchar] (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] [varchar] (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] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[revoke_command] [varchar] (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] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[type_desc] [sys].[sysname] NOT NULL,
[default_schema_name] [varchar] (128) NULL,
[authentication_type_desc] [varchar] (128) NULL,
[grant_command] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[revoke_command] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
)
IF (OBJECT_ID('tempdb..#Cria_Logins') IS NOT NULL) DROP TABLE #Cria_Logins
CREATE TABLE [dbo].[#Cria_Logins] (
[username] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[type_desc] [sys].[sysname] NOT NULL,
[default_database_name] [varchar] (128) NULL,
[default_language_name] [varchar] (128) NULL,
[is_fixed_role] [bit] NULL,
[is_disabled] [BIT] NULL,
[is_policy_checked] [BIT] NULL,
[is_expiration_checked] [BIT] NULL,
[grant_command] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
[revoke_command] [varchar] (MAX) COLLATE SQL_Latin1_General_CP1_CI_AI NULL
)
DECLARE
@Query_Permissao_Database VARCHAR(MAX) = '
SELECT DISTINCT
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] NOT IN (
''dbo'',
''guest'',
''INFORMATION_SCHEMA'',
''sys''
)
AND E.[name] NOT LIKE ''NT SERVICE\%''
AND E.[name] NOT LIKE ''##MS_%'''
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] NOT IN (
''TargetServersRole'',
''SQLAgentUserRole'',
''SQLAgentReaderRole'',
''SQLAgentOperatorRole'',
''DatabaseMailUserRole'',
''db_ssisadmin'',
''db_ssisltduser'',
''db_ssisoperator'',
''dc_operator'',
''dc_admin'',
''dc_proxy'',
''MS_DataCollectorInternalUser'',
''PolicyAdministratorRole'',
''ServerGroupAdministratorRole'',
''ServerGroupReaderRole'',
''##MS_PolicyEventProcessingLogin##'',
''##MS_PolicyTsqlExecutionLogin##'',
''##MS_AgentSigningCertificate##'',
''UtilityCMRReader'',
''UtilityIMRWriter'',
''UtilityIMRReader'',
''db_owner'',
''db_accessadmin'',
''db_securityadmin'',
''db_ddladmin'',
''db_backupoperator'',
''db_datareader'',
''db_datawriter'',
''db_denydatareader'',
''db_denydatawriter'',
''sa'',
''AUTORIDADE NT\SISTEMA'',
''NT AUTHORITY\SYSTEM'',
''dbo'',
''guest'',
''INFORMATION_SCHEMA'',
''sys''
)
AND A.[name] NOT LIKE ''NT SERVICE\%''
AND A.[name] NOT LIKE ''##MS_%'''
IF (@Fl_Permissoes_Database = 1)
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
-- Remove usuários padrão do SQL Server (Não removi no WHERE por causa do limite de 2k caracteres do sp_MSforeachdb)
DELETE FROM #Permissoes_Database
WHERE username IN (
'TargetServersRole',
'SQLAgentUserRole',
'SQLAgentReaderRole',
'SQLAgentOperatorRole',
'DatabaseMailUserRole',
'db_ssisadmin',
'db_ssisltduser',
'db_ssisoperator',
'dc_operator',
'dc_admin',
'dc_proxy',
'MS_DataCollectorInternalUser',
'PolicyAdministratorRole',
'ServerGroupAdministratorRole',
'ServerGroupReaderRole',
'##MS_PolicyEventProcessingLogin##',
'##MS_PolicyTsqlExecutionLogin##',
'##MS_AgentSigningCertificate##',
'UtilityCMRReader',
'UtilityIMRWriter',
'UtilityIMRReader',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'sa',
'AUTORIDADE NT\SISTEMA',
'NT AUTHORITY\SYSTEM'
)
OR (username = 'public' AND ([schema] = 'sys' OR [database] IN ('msdb', 'master')))
OR ([database] IN ('tempdb', 'model'))
---------------------------------------------------------------------------------------
-- 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
END
---------------------------------------------------------------------------------------
-- CRIAÇÃO DE LOGINS
---------------------------------------------------------------------------------------
INSERT INTO #Cria_Logins
SELECT
A.[name] AS username,
A.[type_desc],
A.default_database_name,
A.default_language_name,
A.is_fixed_role,
A.is_disabled,
B.is_policy_checked,
B.is_expiration_checked,
'USE [master]; CREATE LOGIN [' + A.[name] + ']' +
(CASE WHEN A.[type_desc] = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' + COALESCE(UPPER(sys.fn_varbintohexstr(CONVERT(VARBINARY(MAX), password_hash))), '') + ' HASHED,' ELSE ' FROM WINDOWS WITH' END) +
' DEFAULT_DATABASE=[' + A.default_database_name + ']' +
(CASE WHEN A.default_language_name IS NOT NULL THEN ', DEFAULT_LANGUAGE=[' + A.default_language_name + ']' ELSE '' END) +
(CASE WHEN B.is_policy_checked IS NOT NULL THEN ', CHECK_POLICY=ON' ELSE '' END) +
(CASE WHEN B.is_expiration_checked IS NOT NULL THEN ', CHECK_EXPIRATION=ON' ELSE '' END) +
(CASE WHEN A.is_disabled = 1 THEN '; ALTER LOGIN [' + A.[name] + '] DISABLE' ELSE '' END) + ';'
AS GrantCommand,
'USE [master]; DROP LOGIN [' + A.[name] + '];' AS RevokeCommand
FROM
sys.server_principals A WITH(NOLOCK)
LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[name] = B.[name]
WHERE
A.is_fixed_role = 0 -- Não precisa recriar roles padrão do SQL Server
AND A.principal_id > 200 -- Não trazer os usuários padrão do SQL Server
AND A.[name] NOT IN (
'TargetServersRole',
'SQLAgentUserRole',
'SQLAgentReaderRole',
'SQLAgentOperatorRole',
'DatabaseMailUserRole',
'db_ssisadmin',
'db_ssisltduser',
'db_ssisoperator',
'dc_operator',
'dc_admin',
'dc_proxy',
'MS_DataCollectorInternalUser',
'PolicyAdministratorRole',
'ServerGroupAdministratorRole',
'ServerGroupReaderRole',
'##MS_PolicyEventProcessingLogin##',
'##MS_PolicyTsqlExecutionLogin##',
'##MS_AgentSigningCertificate##',
'UtilityCMRReader',
'UtilityIMRWriter',
'UtilityIMRReader',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'sa',
'AUTORIDADE NT\SISTEMA',
'NT AUTHORITY\SYSTEM'
)
AND A.[name] NOT LIKE 'NT SERVICE\%'
AND A.[name] NOT LIKE '##MS_%'
---------------------------------------------------------------------------------------
-- PERMISSÕES NA INSTÂNCIA
---------------------------------------------------------------------------------------
INSERT INTO #Permissoes_Servidor
SELECT DISTINCT
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] NOT IN (
'TargetServersRole',
'SQLAgentUserRole',
'SQLAgentReaderRole',
'SQLAgentOperatorRole',
'DatabaseMailUserRole',
'db_ssisadmin',
'db_ssisltduser',
'db_ssisoperator',
'dc_operator',
'dc_admin',
'dc_proxy',
'MS_DataCollectorInternalUser',
'PolicyAdministratorRole',
'ServerGroupAdministratorRole',
'ServerGroupReaderRole',
'##MS_PolicyEventProcessingLogin##',
'##MS_PolicyTsqlExecutionLogin##',
'##MS_AgentSigningCertificate##',
'UtilityCMRReader',
'UtilityIMRWriter',
'UtilityIMRReader',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'sa',
'AUTORIDADE NT\SISTEMA',
'NT AUTHORITY\SYSTEM'
)
AND A.[name] NOT LIKE 'NT SERVICE\%'
AND A.[name] NOT LIKE '##MS_%'
---------------------------------------------------------------------------------------
-- 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] NOT IN (
'TargetServersRole',
'SQLAgentUserRole',
'SQLAgentReaderRole',
'SQLAgentOperatorRole',
'DatabaseMailUserRole',
'db_ssisadmin',
'db_ssisltduser',
'db_ssisoperator',
'dc_operator',
'dc_admin',
'dc_proxy',
'MS_DataCollectorInternalUser',
'PolicyAdministratorRole',
'ServerGroupAdministratorRole',
'ServerGroupReaderRole',
'##MS_PolicyEventProcessingLogin##',
'##MS_PolicyTsqlExecutionLogin##',
'##MS_AgentSigningCertificate##',
'UtilityCMRReader',
'UtilityIMRWriter',
'UtilityIMRReader',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'sa',
'AUTORIDADE NT\SISTEMA',
'NT AUTHORITY\SYSTEM'
)
AND A.[name] NOT LIKE 'NT SERVICE\%'
AND A.[name] NOT LIKE '##MS_%'
---------------------------------------------------------------------------------------
-- CRIA OS USUÁRIOS DE DATABASE
---------------------------------------------------------------------------------------
DECLARE @Comando VARCHAR(MAX) = ''
IF (@Fl_Permissoes_Database = 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() + '']; '' + (CASE WHEN A.[type_desc] LIKE ''%ROLE%'' THEN ''CREATE ROLE ['' + A.[name] + ''];'' ELSE ''CREATE USER ['' + A.[name] + '']'' + (CASE WHEN A.[type_desc] = ''SQL_USER'' AND B.[name] IS NOT NULL THEN '' FOR LOGIN ['' + B.[name] + '']'' WHEN A.[type_desc] = ''SQL_USER'' THEN '''' ELSE '' FOR LOGIN ['' + A.[name] + '']'' END) + '' WITH DEFAULT_SCHEMA=['' + ISNULL(a.default_schema_name, ''dbo'') + ''];'' END) AS GrantCommand,
''USE ['' + DB_NAME() + '']; DROP '' + (CASE WHEN A.[type_desc] LIKE ''%ROLE%'' THEN ''ROLE'' ELSE ''USER'' END) + '' ['' + A.[name] + ''];'' AS RevokeCommand
FROM
sys.database_principals A WITH(NOLOCK)
LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
WHERE
A.[name] NOT IN (
''TargetServersRole'',
''SQLAgentUserRole'',
''SQLAgentReaderRole'',
''SQLAgentOperatorRole'',
''DatabaseMailUserRole'',
''db_ssisadmin'',
''db_ssisltduser'',
''db_ssisoperator'',
''dc_operator'',
''dc_admin'',
''dc_proxy'',
''MS_DataCollectorInternalUser'',
''PolicyAdministratorRole'',
''ServerGroupAdministratorRole'',
''ServerGroupReaderRole'',
''##MS_PolicyEventProcessingLogin##'',
''##MS_PolicyTsqlExecutionLogin##'',
''##MS_AgentSigningCertificate##'',
''UtilityCMRReader'',
''UtilityIMRWriter'',
''UtilityIMRReader'',
''db_owner'',
''db_accessadmin'',
''db_securityadmin'',
''db_ddladmin'',
''db_backupoperator'',
''db_datareader'',
''db_datawriter'',
''db_denydatareader'',
''db_denydatawriter'',
''sa'',
''AUTORIDADE NT\SISTEMA'',
''NT AUTHORITY\SYSTEM'',
''dbo'',
''guest'',
''INFORMATION_SCHEMA'',
''sys'',
''public''
)'
INSERT INTO #Cria_Usuarios
EXEC master.dbo.sp_MSforeachdb @Query_Cria_Usuarios
END
---------------------------------------------------------------------------------------
-- GERA OS ARQUIVOS
---------------------------------------------------------------------------------------
-- Ativa o OLE Automation (caso não esteja ativo)
IF (@Fl_Ole_Automation_Ativo = 0)
BEGIN
EXEC sp_configure 'show advanced options', 1
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;
END
DECLARE
@Nm_Arquivo VARCHAR(1000),
@ConteudoArquivo VARCHAR(MAX) = ''
IF (@Fl_Arquivo_Unico = 1)
BEGIN
SET @Nm_Arquivo = @Ds_Diretorio_Saida + 'sql_logins_' + @@SERVICENAME + '.sql'
SET @ConteudoArquivo += '-- CRIAÇÃO DOS LOGINS
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Cria_Logins
ORDER BY username
SET @ConteudoArquivo += '
-- PERMISSÕES NA INSTÂNCIA
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Servidor
ORDER BY username
SET @ConteudoArquivo += '
-- PERMISSÕES NAS ROLES DA INSTÂNCIA
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Roles_Servidor
ORDER BY username
IF (@Fl_Permissoes_Database = 1)
BEGIN
SET @ConteudoArquivo += '
-- CRIA USUÁRIOS NOS DATABASES' + '
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Cria_Usuarios
ORDER BY [database], username
SET @ConteudoArquivo += '
-- PERMISSÕES NOS DATABASES
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Database
ORDER BY [database], [username]
SET @ConteudoArquivo += '
-- PERMISSÕES NAS ROLES DOS DATABASES' + '
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Roles
ORDER BY [database], [role], [username]
END
EXEC dbo.stpEscreve_Arquivo_FSO
@String = @ConteudoArquivo, -- varchar(max)
@Ds_Arquivo = @Nm_Arquivo -- varchar(1501)
END
ELSE BEGIN
IF (OBJECT_ID('tempdb..#Permissoes_por_Usuario') IS NOT NULL) DROP TABLE #Permissoes_por_Usuario
CREATE TABLE #Permissoes_por_Usuario (
ranking INT IDENTITY(1, 1) NOT NULL,
username VARCHAR(128) NOT NULL
)
INSERT INTO #Permissoes_por_Usuario
SELECT DISTINCT username COLLATE SQL_Latin1_General_CP1_CI_AI
FROM #Cria_Logins
UNION
SELECT DISTINCT username COLLATE SQL_Latin1_General_CP1_CI_AI
FROM #Cria_Usuarios
UNION
SELECT DISTINCT username COLLATE SQL_Latin1_General_CP1_CI_AI
FROM #Permissoes_Database
UNION
SELECT DISTINCT username COLLATE SQL_Latin1_General_CP1_CI_AI
FROM #Permissoes_Roles
DECLARE
@Contador INT = 1,
@Total INT = (SELECT COUNT(*) FROM #Permissoes_por_Usuario),
@Usuario VARCHAR(128)
WHILE(@Contador <= @Total)
BEGIN
SELECT @Usuario = username
FROM #Permissoes_por_Usuario
WHERE ranking = @Contador
SET @Nm_Arquivo = @Ds_Diretorio_Saida + REPLACE(@@SERVERNAME, '\', '-') + '_' + REPLACE(@Usuario, '\', '-') + '.sql'
SET @ConteudoArquivo = '-- CRIAÇÃO DOS LOGINS
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Cria_Logins
WHERE username = @Usuario
ORDER BY username
SET @ConteudoArquivo += '
-- PERMISSÕES NA INSTÂNCIA
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Servidor
WHERE username = @Usuario
ORDER BY username
SET @ConteudoArquivo += '
-- PERMISSÕES NAS ROLES DA INSTÂNCIA
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Roles_Servidor
WHERE username = @Usuario
ORDER BY username
IF (@Fl_Permissoes_Database = 1)
BEGIN
SET @ConteudoArquivo += '
-- CRIA USUÁRIOS NOS DATABASES
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Cria_Usuarios
WHERE username = @Usuario
ORDER BY [database], username
SET @ConteudoArquivo += '
-- PERMISSÕES NOS DATABASES
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Database
WHERE username = @Usuario
ORDER BY username
SET @ConteudoArquivo += '
-- PERMISSÕES NAS ROLES DOS DATABASES
'
SELECT TOP 9999999999 @ConteudoArquivo += grant_command + '
'
FROM #Permissoes_Roles
WHERE username = @Usuario
ORDER BY username
END
EXEC dbo.stpEscreve_Arquivo_FSO
@String = @ConteudoArquivo, -- varchar(max)
@Ds_Arquivo = @Nm_Arquivo -- varchar(1501)
SET @Contador += 1
END
END
-- Desativa o OLE Automation (Caso esteja desativado antes de executar a SP)
IF (@Fl_Ole_Automation_Ativo = 0)
BEGIN
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
END
END
Usage examples
Example 1: Exporting 1 script per user
EXEC dbo.stpExporta_Logins
@Ds_Diretorio_Saida = 'C:\Teste\', -- varchar(500)
@Fl_Permissoes_Database = 1, -- bit
@Fl_Arquivo_Unico = 0 -- bit
Generated files:
User with Windows AD authentication:
User with SQL authentication and password security and expiration policies:
User with SQL authentication and without login:
User with SQL authentication and different user login:
Example 2: Exporting 1 script per instance
EXEC dbo.stpExporta_Logins
@Ds_Diretorio_Saida = 'C:\Teste\', -- varchar(500)
@Fl_Permissoes_Database = 1, -- bit
@Fl_Arquivo_Unico = 1 -- bit
Generated files:
View of the generated script:
That's it, folks.
I hope this post was useful for you.
Hug!
SQL Server – How to transfer logins between instances by generating backup of users, logins and permissions reverse engineering backup logins users permissions roles server database migrate logins transfer logins users
SQL Server – How to transfer logins between instances by generating backup of users, logins and permissions reverse engineering backup logins users permissions roles server database migrate logins transfer logins users
Comentários (0)
Carregando comentários…