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:

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:

-- 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'

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!

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:

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!