Identify and fix orphanated users

Já restaurou uma database em outro ambiente e precisou ajustar os usuários órfãos?

Os usuários órfãos existem para logins que são criados no nível de instância do SQL Server e portanto são armazenados na database master. Os usuários acabam se tornando órfãos quando você restaura essa database em outra instância e o login não existe no nível de instância, mas existe no nível de database.

Para identificar os usuários órfãos em uma determinada database você pode utilizar a consulta abaixo:


SELECT dp.type_desc, dp.SID, dp.name AS user_name
FROM sys.database_principals AS dp
LEFT JOIN sys.server_principals AS sp ON dp.SID = sp.SID
WHERE sp.SID IS NULL AND authentication_type_desc = 'INSTANCE'

Retorno:

Screen Shot 2019-07-30 at 20.39.34.png

Para corrigir um usuário órfão você pode usar as duas opções abaixo:

Opção 1:


CREATE LOGIN [loginName] WITH PASSWORD = 'loginPassword', SID = 0xF4E44137DEB63E4783109C6C06E80BBE

ALTER USER [userName] WITH Login = [loginName]

OBS: O valor do parâmetro SID deve ser preenchido com o valor capturado a partir da consulta anterior utilizada para identificar os usuários órfãos.

Opção 2:


EXECUTE sp_change_users_login AUTO_FIX, 'loginUsername'

Referências:

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-change-users-login-transact-sql?view=sql-server-2017

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s