Usuários órfãos no SQL Server? Evite este problema!

Depois de um bom tempo tentando resolver problemas de usuários órfãos após backups, migração de bancos, log shipping, mirroring e outras ocorrências, pude verificar em diversos blogs e artigos na internet algumas formas de resolver este problema, mas não vi formas de evitar que ele aconteça… O que me deixou impressionado, pois o problema é bem mais simples do que parece (depois que você descobre como funciona, realmente parece simples).

Um exemplo comum de usuário órfão:

Usuário Órfão

Solução para o problema:

Para quem chegou a este artigo procurando solucionar um problema de usuários órfãos, você possui várias alternativas:

No SQL Server 2000 e SQL Server 2005:

-- Associando a um login existente:
EXEC sp_change_users_login 'Update_One', 'nome do usuário', 'nome do login'

 

--Associando a um login existente com o mesmo nome do usuário,
--Ou se não existir login com o mesmo nome do usuário, criar um com a senha informada:
EXEC sp_change_users_login 'Auto_Fix', 'nome do usuário', NULL, 'senha'

(ref.: http://msdn.microsoft.com/pt-br/library/ms175475.aspx)

A partir do SQL Server 2005 e SQL Server 2008:

-- Associando a um login existente:
ALTER USER [nome do usuario] WITH LOGIN = [nome do login] 

(ref.: http://msdn.microsoft.com/en-us/library/ms176060.aspx)

Agora vamos saber por que isso ocorre:

Os usuários do banco de dados são associados a um código de segurança (SID) dos logins da instância do banco de dados, mas como este SID pode ser aleatório na criação dos logins, o fato de você possuir um login com mesmo nome em duas instâncias do SQL Server distintas, não quer dizer eles são iguais, pois quando o SQL Server tenta restaurar um banco de dados de outra instância, ele só consegue identificar os logins “pais” de seus usuários pelo SID.

Mas se eu forçar um SID para o meu login nas instâncias do SQL Server, será que ainda vou ter o problema? Então vamos conferir:

Utilizei a seguinte consulta para recuperar o SID de um determinado login:

SELECT name, sid FROM sys.server_principals WHERE type = 'S'

SELECT name, sid FROM sys.server_principals WHERE type = 'S'

Em outro SQL Server, vou criar um login com mesmo SID:

CREATE LOGIN paulo
WITH PASSWORD = 'p@$$w0rd',
SID = 0x0F5AE6C15103B647A7BD41F744C256F3

Após restaurar o banco de dados neste outro servidor, como resultado, sem usuário órfão!

Usuário Adotado

E se você tiver um login com nome diferente do login da outra instância, mas com um mesmo SID, o SQL Server ainda utilizará o SID como critério para associar os usuários aos seus respectivos logins, exemplo:

Usuário Estranhamente Adotado

Então, criar logins com SIDs iguais entre instâncias do SQL Server, evitará que problemas de usuários órfãos ocorram novamente, seja por backups, migração de bancos, log shipping e mirroring! A partir de agora, só diversão!

Anúncios

8 pensamentos sobre “Usuários órfãos no SQL Server? Evite este problema!

  1. Agora só preciso saber se tem opção de recuperar o usuário órfão sem ter que trocar a senha dele …

  2. Pingback: Aniversário de 3 anos do SQL From Hell!! « SQL From Hell.com

  3. Cara, valeu mesmo, eu ja tinha tentado de tudo e na verdade era uma coisa tão simples. Me ajudou muito. Obrigado pelo post.

  4. Pingback: Gerando o script de CREATE LOGIN com os SIDs | SQL From Hell.com

  5. Pingback: Usuários órfãos no SQL Server « SP_HELP_DB

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s