Identificando e resolvendo problemas de usuários órfãos no SQL Server com a sp_change_users_login

Tempo de Leitura: 7 minutos

Olá Pessoal,
Tudo certo ?

Neste post vou falar um pouco sobre um problema que tenho visto bastante nas bases em que tenho prestado consultoria, que são os usuários órfãos na base.

Um pouco sobre permissões no SQL Server

No SQL Server, existe o conceito de Login, que é o usuário a nível de instância de banco e também é o objeto que é associado à senha, às políticas de expiração e segurança de senha, às permissões a nível de servidor, etc.

Além disso, existe também o conceito de usuário, que é criado para cada database da instância e guarda os privilégios do database em questão. Sendo assim, uma pessoa possui um Login na instância e um ou mais usuários criados, pois para cada database, ele deverá ter o seu usuário (não precisa ser o mesmo nome) para receber permissões específicas em cada database (a não ser que o Login da pessoa esteja em alguma role que lhe dê privilégios em todos os database, como a role de administrador sysadmin).

O SQL Server ainda possui o conceito de grupo do AD, onde caso você faça parte um grupo do AD, e esse grupo for criado no SQL Server em forma de um login, não será necessária a criação do login de cada membro do grupo do AD, apenas do próprio grupo. Neste caso, todos os membros desse grupo terão as mesmas permissões a nível de instância e poderão se conectar ao banco usando seu usuário e senha do AD. (Caso o grupo tenha privilégios para isso)

O que são usuários órfãos?

Na teoria, todos os usuários, de todos os databases, devem referenciar algum login para que seja possível utilizar esse login para se autenticar na base de dados. Quando existe algum usuário que não possua essa referência, chamamos esse usuário de órfão.

Uma coisa que deve ficar bem clara quando encontramos um usuário SQL Server sem login é que existe uma diferença entre usuário órfão e usuário sem login:

Usuário órfão: Esse usuário SQL foi criado sendo mapeado a um login. Por algum motivo (possivelmente um restore do banco) esse usuário perdeu a associação com o login e ficou nesse estado de usuário sem login. Esse tipo de usuário pode ser remapeado ao login, caso essa associação se perca, e pode ser utilizado para logar no SQL Server.
Usuário sem login: Esse usuário SQL Server foi criado utilizando a opção “SQL User without login” e não foi associado a nenhum login durante a criação. Nesse cenário, não é possível mapear esse usuário a um login (a não ser que você apague o usuário, crie de novo associando a um login e reaplique as permissões que ele tinha), e com isso, não é possível logar no SQL Server utilizando esse usuário.
Esse cenário é muito utilizado por motivos de segurança, onde é necessário um usuário para ser o owner dos objetos, mas não se deseja ser possível utilizá-lo para logar no banco ou realizar alterações de dados ou estruturas, como podemos ler no artigo do Pinal Dave nesse link aqui.

Como isso acontece?

O SQL Server tenta impedir que você crie um usuário sem especificar o respectivo login:

CREATE USER [Teste]
GO

1 2	CREATE USER [Teste] GO

Msg 15007, Level 16, State 1, Line 1
‘Teste’ is not a valid login or you do not have permission.

Agora vamos criar direito:

USE [master]
CREATE LOGIN [Usuario_Teste] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [Testes]
CREATE USER [Usuario_Orfao] FOR LOGIN [Usuario_Teste] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [master]

CREATE LOGIN [Usuario_Teste] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE [Testes]

CREATE USER [Usuario_Orfao] FOR LOGIN [Usuario_Teste] WITH DEFAULT_SCHEMA=[dbo]

Pronto. Criamos nosso login e nosso usuário:

Agora, o que acontece com o usuário se excluirmos o seu login ?

USE [master]
DROP LOGIN [Usuario_Teste]
GO

USE [master]

DROP LOGIN [Usuario_Teste]

Ou seja, dessa forma, mesmo que tenhamos nosso usuário criado, não conseguiremos logar no banco, pois o nosso login não existe mais. Temos então, um usuário órfão na base. E isso é um problema, pois é um usuário “inútil” no banco, pois sem o login, não é possível se conectar no banco.

Como identificar os usuários órfãos de um database?

USE [Testes]
EXEC sp_change_users_login 'Report'

1 2	USE [Testes] EXEC sp_change_users_login 'Report'

Ou utilizando a sys.syslogins (SQL Server 2000 em diante):

SELECT
    A.name AS UserName,
    A.[sid] AS UserSID
FROM
    sys.sysusers A WITH(NOLOCK)
    LEFT JOIN sys.syslogins B WITH(NOLOCK) ON A.[sid] = B.[sid]
WHERE
    A.issqluser = 1 
    AND SUSER_NAME(A.[sid]) IS NULL 
    AND IS_MEMBER('db_owner') = 1 
    AND A.[sid] != 0x00
    AND A.[sid] IS NOT NULL 
    AND ( LEN(A.[sid]) <= 16 ) 
    AND B.[sid] IS NULL
ORDER BY
    A.name

SELECT

A.name AS UserName,

A.[sid] AS UserSID

FROM

sys.sysusers A WITH(NOLOCK)

LEFT JOIN sys.syslogins B WITH(NOLOCK) ON A.[sid] = B.[sid]

WHERE

A.issqluser = 1

AND SUSER_NAME(A.[sid]) IS NULL

AND IS_MEMBER('db_owner') = 1

AND A.[sid] != 0x00

AND A.[sid] IS NOT NULL

AND ( LEN(A.[sid]) <= 16 )

AND B.[sid] IS NULL

ORDER BY

A.name

Ou utilizando a sys.database_principals (SQL Server 2008 em diante):

SELECT
    A.name AS UserName,
    A.[sid] AS UserSID
FROM
    sys.database_principals A WITH(NOLOCK)
    LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.name NOT LIKE '##MS_%'
    AND A.[type_desc] = 'SQL_USER'
    AND C.[type_desc] = 'SQL_LOGIN'
    AND A.name NOT IN ('sa')
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name

SELECT

A.name AS UserName,

A.[sid] AS UserSID

FROM

sys.database_principals A WITH(NOLOCK)

LEFT JOIN sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]

JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI

WHERE

A.principal_id > 4

AND B.[sid] IS NULL

AND A.is_fixed_role = 0

AND C.is_fixed_role = 0

AND A.name NOT LIKE '##MS_%'

AND A.[type_desc] = 'SQL_USER'

AND C.[type_desc] = 'SQL_LOGIN'

AND A.name NOT IN ('sa')

AND A.authentication_type <> 0 -- NONE

ORDER BY

A.name

Já recriei o login. Como faço para recriar a associação Login x Usuário ?

USE [Testes]
EXEC sp_change_users_login 'Auto_Fix', 'Usuario_Orfao' -- Isso irá associar o Login 'Usuario_Orfao' ao usuário 'Usuario_Orfao'
GO

USE [Testes]

EXEC sp_change_users_login 'Auto_Fix', 'Usuario_Orfao' -- Isso irá associar o Login 'Usuario_Orfao' ao usuário 'Usuario_Orfao'

Em caso de sucesso, o SQL Server irá retornar as mensagens abaixo e executando novamente o comando EXEC sp_change_users_login ‘Report’, esse usuário não aparecerá mais nas linhas retornadas:

The row for user ‘Usuario_Orfao’ will be fixed by updating its login link to a login already in existence.
The number of orphaned users fixed by updating users was 1.
The number of orphaned users fixed by adding new logins and then updating users was 0.

Recriando o login de todos os usuários, de todos os databases, da sua instância

-- Identificando todos os usuários órfãos da instância
IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos
CREATE TABLE #Usuarios_Orfaos (
    [Ranking] INT IDENTITY(1,1),
    [Database] sysname,
    [Username] sysname,
    [SID] UNIQUEIDENTIFIER,
    [Command] VARCHAR(MAX)
)

INSERT INTO #Usuarios_Orfaos
EXEC master.dbo.sp_MSforeachdb '
SELECT
    ''?'' AS [database],
    A.name,
    A.[sid],
    ''EXEC [?].[sys].[sp_change_users_login] ''''Auto_Fix'''', '''''' + A.name + '''''''' AS command
FROM
    [?].sys.database_principals A WITH(NOLOCK)
    LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.name NOT LIKE ''##MS_%''
    AND A.[type_desc] = ''SQL_USER''
    AND C.[type_desc] = ''SQL_LOGIN''
    AND A.name NOT IN (''sa'')
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name'


-- Exibindo os usuários órfãos da instância
SELECT * FROM #Usuarios_Orfaos


-- Executando os comandos no banco
DECLARE @Comando VARCHAR(MAX) = ''

SELECT @Comando += Command + '; '
FROM #Usuarios_Orfaos

EXEC(@Comando)

-- Identificando todos os usuários órfãos da instância

IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos

CREATE TABLE #Usuarios_Orfaos (

[Ranking] INT IDENTITY(1,1),

[Database] sysname,

[Username] sysname,

[SID] UNIQUEIDENTIFIER,

[Command] VARCHAR(MAX)

)

INSERT INTO #Usuarios_Orfaos

EXEC master.dbo.sp_MSforeachdb '

SELECT

''?'' AS [database],

A.name,

A.[sid],

''EXEC [?].[sys].[sp_change_users_login] ''''Auto_Fix'''', '''''' + A.name + '''''''' AS command

FROM

[?].sys.database_principals A WITH(NOLOCK)

LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]

JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI

WHERE

A.principal_id > 4

AND B.[sid] IS NULL

AND A.is_fixed_role = 0

AND C.is_fixed_role = 0

AND A.name NOT LIKE ''##MS_%''

AND A.[type_desc] = ''SQL_USER''

AND C.[type_desc] = ''SQL_LOGIN''

AND A.name NOT IN (''sa'')

AND A.authentication_type <> 0 -- NONE

ORDER BY

A.name'

-- Exibindo os usuários órfãos da instância

SELECT * FROM #Usuarios_Orfaos

-- Executando os comandos no banco

DECLARE @Comando VARCHAR(MAX) = ''

SELECT @Comando += Command + '; '

FROM #Usuarios_Orfaos

EXEC(@Comando)

Já recriei o login, mas ele não possui o mesmo nome do usuário. Como faço para recriar a associação Login x Usuário ?

USE [Testes]
EXEC sp_change_users_login 
    'Update_One', 
    'Usuario_Orfao',  -- Usuário
    'Usuario_Teste'   -- Login
GO

USE [Testes]

EXEC sp_change_users_login

'Update_One',

'Usuario_Orfao', -- Usuário

'Usuario_Teste' -- Login

Já existe um usuário criado. Quero associá-lo a um novo login que vou criar em tempo de execução.

USE [Testes]
EXEC sp_change_users_login 
    'Auto_Fix', 
    'Usuario_Orfao',  -- Usuário
    NULL,             -- Login. Deixar NULL para criar um novo com o mesmo nome do usuário
    '123'             -- Senha do Login que será criado
GO

USE [Testes]

EXEC sp_change_users_login

'Auto_Fix',

'Usuario_Orfao', -- Usuário

NULL, -- Login. Deixar NULL para criar um novo com o mesmo nome do usuário

'123' -- Senha do Login que será criado

Retorno do SQL Server:

Barring a conflict, the row for user ‘Usuario_Orfao’ will be fixed by updating its link to a new login.
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 1.

An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

Erro comum que ocorre no SQL Server quando você tenta realizar o AUTO_FIX de um usuário órfão e esse usuário já foi criado com a opção WITHOUT LOGIN. Neste caso, o Auto_Fix não irá corrigir o problema e irá retornar essa mensagem de erro:

Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 214
An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’.

Nesse caso, a solução é corrigir o problema manualmente, utilizando as soluções apresentadas acima.

The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

Uma coisa que deve ficar bem clara quando encontramos um usuário SQL Server sem login é que existe uma diferença entre usuário órfão e usuário sem login:

Usuário órfão: Esse usuário SQL foi criado sendo mapeado a um login. Por algum motivo (possivelmente um restore do banco) esse usuário perdeu a associação com o login e ficou nesse estado de usuário sem login. Esse tipo de usuário pode ser remapeado ao login, caso essa associação se perca, e pode ser utilizado para logar no SQL Server.
Usuário sem login: Esse usuário SQL Server foi criado utilizando a opção “SQL User without login” e não foi associado a nenhum login durante a criação. Nesse cenário, não é possível mapear esse usuário a um login, e com isso, não é possível logar no SQL Server utilizando esse usuário. Esse cenário é muito utilizado por motivos de segurança, onde é necessário um usuário para ser o owner dos objetos, mas não se deseja ser possível utilizá-lo para logar no banco ou realizar alterações de dados ou estruturas. Caso você tente mapear esse tipo de usuário a um login, irá se deparar com a mensagem de erro acima.

ALTER USER… WITH LOGIN

Uma outra forma de corrigir usuários órfãos é utilizar o comando ALTER USER:

ALTER USER [Usuario] WITH LOGIN = [Login]

1	ALTER USER [Usuario] WITH LOGIN = [Login]

Para identificar e corrigir os usuários órfãos da instância, em todos os databases, com o comando ALTER USER, utilize o script abaixo:

-- Identificando todos os usuários órfãos da instância
IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos
CREATE TABLE #Usuarios_Orfaos (
    [Ranking] INT IDENTITY(1,1),
    [Database] sysname,
    [Username] sysname,
    [SID] UNIQUEIDENTIFIER,
    [Command] VARCHAR(MAX)
)

INSERT INTO #Usuarios_Orfaos
EXEC master.dbo.sp_MSforeachdb '
SELECT
    ''?'' AS [database],
    A.name,
    A.[sid],
    ''USE [?]; ALTER USER ['' + A.[name] + ''] WITH LOGIN = ['' + A.[name] + '']'' AS command
FROM
    [?].sys.database_principals A WITH(NOLOCK)
    LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]
    JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI
WHERE
    A.principal_id > 4
    AND B.[sid] IS NULL
    AND A.is_fixed_role = 0
    AND C.is_fixed_role = 0
    AND A.[type_desc] = ''SQL_USER''
    AND C.[type_desc] = ''SQL_LOGIN''
    AND A.authentication_type <> 0 -- NONE
ORDER BY
    A.name'

SELECT * FROM #Usuarios_Orfaos

-- Identificando todos os usuários órfãos da instância

IF (OBJECT_ID('tempdb..#Usuarios_Orfaos') IS NOT NULL) DROP TABLE #Usuarios_Orfaos

CREATE TABLE #Usuarios_Orfaos (

[Ranking] INT IDENTITY(1,1),

[Database] sysname,

[Username] sysname,

[SID] UNIQUEIDENTIFIER,

[Command] VARCHAR(MAX)

)

INSERT INTO #Usuarios_Orfaos

EXEC master.dbo.sp_MSforeachdb '

SELECT

''?'' AS [database],

A.name,

A.[sid],

''USE [?]; ALTER USER ['' + A.[name] + ''] WITH LOGIN = ['' + A.[name] + '']'' AS command

FROM

[?].sys.database_principals A WITH(NOLOCK)

LEFT JOIN [?].sys.sql_logins B WITH(NOLOCK) ON A.[sid] = B.[sid]

JOIN sys.server_principals C WITH(NOLOCK) ON A.[name] COLLATE SQL_Latin1_General_CP1_CI_AI = C.[name] COLLATE SQL_Latin1_General_CP1_CI_AI

WHERE

A.principal_id > 4

AND B.[sid] IS NULL

AND A.is_fixed_role = 0

AND C.is_fixed_role = 0

AND A.[type_desc] = ''SQL_USER''

AND C.[type_desc] = ''SQL_LOGIN''

AND A.authentication_type <> 0 -- NONE

ORDER BY

A.name'

SELECT * FROM #Usuarios_Orfaos

Resultado:

Observação: Eu prefiro esse método, pois a sp_change_users_login está marcada como deprecated e pode ser removida em futuras edições do SQL Server.

Conclusão

Lembre-se que o SQL Server possui o conceito de Grupos de Acesso vindos do AD. Se existir um Login para um grupo do AD na sua instância e você precisar liberar acessos individuais nos databases para determinados usuários, provavelmente esses usuários não terão associação com Login, sendo portanto, usuários órfãos.

Nesse caso, esses usuários órfãos não possuem efeito negativo na sua base, pois eles acessam normalmente o banco através do grupo do AD, e possuem permissões individuais em determinados databases.

Então, tome muito cuidado na hora de identificar e remover/corrigir usuários órfãos, pois os comandos vistos acima podem retornar falsos-positivos para usuários órfãos nessa situação.

É isso aí,
Até a próxima!

Other Languages

Assinar blog por e-mail

Visualizações do Blog

Categorias

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

Identificando e resolvendo problemas de usuários órfãos no SQL Server com a sp_change_users_login

Um pouco sobre permissões no SQL Server

O que são usuários órfãos?

Como isso acontece?

Como identificar os usuários órfãos de um database?

Já recriei o login. Como faço para recriar a associação Login x Usuário ?

Recriando o login de todos os usuários, de todos os databases, da sua instância

Já recriei o login, mas ele não possui o mesmo nome do usuário. Como faço para recriar a associação Login x Usuário ?

Já existe um usuário criado. Quero associá-lo a um novo login que vou criar em tempo de execução.

An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

ALTER USER… WITH LOGIN

Conclusão

You may also like...

Deixe uma resposta Cancelar resposta

Um pouco sobre permissões no SQL Server

O que são usuários órfãos?

Como isso acontece?

Como identificar os usuários órfãos de um database?

Já recriei o login. Como faço para recriar a associação Login x Usuário ?

Recriando o login de todos os usuários, de todos os databases, da sua instância

Já recriei o login, mas ele não possui o mesmo nome do usuário. Como faço para recriar a associação Login x Usuário ?

Já existe um usuário criado. Quero associá-lo a um novo login que vou criar em tempo de execução.

An invalid parameter or option was specified for procedure ‘sys.sp_change_users_login’

The user cannot be remapped to a login. Remapping can only be done for users that were mapped to Windows or SQL logins.

ALTER USER… WITH LOGIN

Conclusão

You may also like...

SQL Server – Como converter uma string HTML para texto (Remover tags HTML) utilizando o CLR (C#)

As procedures estendidas não documentadas do SQL Server

SQL Server – Entendendo as permissões e roles do SQL Agent (SQLAgentUserRole, SQLAgentReaderRole e SQLAgentOperatorRole)

Deixe uma resposta Cancelar resposta