Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server – Como transferir logins entre instâncias gerando backup de usuários, logins e permissões

Post Views 6,080 views
Reading time 11 minutes

Olá pessoal,
Tudo bem com vocês ?

Neste post vou demonstrar um script bem legal que eu desenvolvi e que tem por objetivo, gerar scripts SQL para backup (engenharia reversa) de permissões, usuários, logins e roles de uma instância no SQL Server.

Esse script foi idealizado para ser utilizado em migrações de instâncias, seja para um novo servidor ou para um upgrade de versão da sua instância SQL Server, de uma forma prática e fácil de se utilizar. Caso você também queira migrar os jobs do SQL Agent para outra instância também, veja mais acessando o post SQL Server – Como fazer backup de todos os jobs do SQL Agent via linha de comando (CLR C# ou Powershell).

Utilizando a Stored Procedure abaixo, é possível gerar comandos SQL para realizar as seguintes tarefas:

  • Script para backup da criação dos logins (Autenticação Windows AD e SQL Server)
  • Script para backup das permissões do login da instância (Server level permissions)
  • Script para backup das permissões do login em roles da instância (Server roles permissions)
  • Script para backup da criação dos usuários em databases (COM e SEM login)
  • Script para backup das permissões do login na database (Database level permissions)
  • Script para backup das permissões do login em roles da database (Database roles permissions)

Como pré-requisitos para a utilização da stpExporta_Logins, você precisará de:

O script da stpExporta_Logins, que eu vou apresentar neste post, foi inspirado na Stored Procedure do post SQL Server – Como copiar/replicar as permissões de um usuário, onde a procedure deste post tem o objetivo de gerar arquivos SQL para replicar as permissões para outro ambiente, enquanto o script do outro post tem como objetivo copiar as permissões de um usuário para outro na mesma instância e por isso, não tem várias validações e verificações, como criação de logins.

Código-fonte da Procedure stpExporta_Logins

Para realizar todas as tarefas de forma fácil, basta executar o código-fonte abaixo e criar a stpExporta_Logins em um database de sua escolha.

Essa Stored Procedure utiliza a SP stpEscreve_Arquivo_FSO para gravar os arquivos fisicamente no disco, o que exige que o recurso ‘Ole Automation Procedures’ esteja habilitado na instância.

Porém, não precisa se preocupar com essa parte pois a SP tem uma verificação que, caso esse recurso não esteja ativado na instância, a própria SP vai ativá-lo durante a execução e irá desativá-lo após a geração dos arquivos, mantendo essa configuração do mesmo jeito que estava antes da execução da Procedure.

Código-fonte:

Exemplos de uso

Exemplo 1: Exportando 1 script por usuário

Arquivos gerados:

Usuário com autenticação Windows AD:

Usuário com autenticação SQL e políticas de expiração e segurança de senha:

Usuário com autenticação SQL e sem login:

Usuário com autenticação SQL e login diferente do usuário:

Exemplo 2: Exportando 1 script por instância

Arquivos gerados:

Visualização do script gerado:

É isso aí, pessoal.
Espero que esse post tenha sido útil para vocês.

Abraço!

SQL Server – Como transferir logins entre instâncias gerando backup de usuários, logins e permissões engenharia reversa backup logins users permissions roles server database migrate logins transfer logins users

SQL Server – Como transferir logins entre instâncias gerando backup de usuários, logins e permissões engenharia reversa backup logins users permissions roles server database migrate logins transfer logins users