Skip to content

Dirceu Resende

DBA SQL Server e Analista de BI (PowerBI, SSAS, SSIS, SSRS)

  • Consultoria
    • Consultoria de BI
    • Consultoria de Power BI
    • Consultoria SQL Server
  • Arquivo
  • Séries
    • Certificação
    • Segurança e Auditoria
    • Performance Tuning
    • O que mudou no T-SQL ?
    • Proteção de Dados
  • Vagas de Emprego
  • Eventos Data Platform
  • Sobre
  • Contato

Other Languages

Assinar blog por e-mail

Digite seu endereço de e-mail para assinar este blog e receber notificações de novas publicações por e-mail.

Junte-se a 536 outros assinantes

Visualizações do Blog

1.645.456 views

Categorias

  • Apache / .htaccess (9)
  • Banco de Dados (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Auditoria (15)
      • Azure (2)
      • CLR (53)
      • Desenvolvimento de Query (83)
      • DMVs e Views de Catálogo (31)
      • Erros (22)
      • Ferramentas (12)
      • Formatação e Validação de Dados (23)
      • Funcionalidades pouco conhecidas (19)
      • Hacks (17)
      • Integrações (30)
      • Manipulação de Arquivos (13)
      • Manutenção (80)
      • Monitoramento (35)
      • O que não fazer (7)
      • OLE Automation (19)
      • Performance Tuning (22)
      • Python (1)
      • Segurança (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Carreira e Cursos (13)
  • Carreira, Cursos e Certificações (28)
  • Celulares (1)
  • Eventos e Palestras (63)
  • Programação (57)
    • C# (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • Javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Sem categoria (10)
  • SEO (4)
  • Virtualização (5)

Microsoft MVP Data Platform

Minhas Certificações

Treinamentos

Arquivo de Posts

Posts recentes

  • Descontos da “Black Friday” nos Treinamentos de SQL Server (Comprem meu curso kkkkk) 27 de novembro de 2020
  • SQL Server – As “novas” funções GREATEST e LEAST 27 de novembro de 2020
  • SQL Server – Como saber a data do último login de um usuário 9 de novembro de 2020
  • Azure na Prática Gratuito #07 – Administrando Banco de Dados no Azure 5 de novembro de 2020
  • Analysis Services – An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file ‘tmcachemanager.cpp’, function ‘TMCacheManager::CreateEmptyCollectionsForAllParents’) 5 de novembro de 2020
  • 8 de fevereiro de 2015
  • 0
  • Banco de Dados Manutenção SQL Server

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

Visualizações: 3.009
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:

Transact-SQL
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:

Transact-SQL
1
2
3
4
5
6
7
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

Pronto. Criamos nosso login e nosso usuário:

SQL_Server_Usuario_Orfao1

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

Transact-SQL
1
2
3
USE [master]
DROP LOGIN [Usuario_Teste]
GO

SQL_Server_Usuario_Orfao2

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?

Transact-SQL
1
2
USE [Testes]
EXEC sp_change_users_login 'Report'

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

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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):

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

SQL_Server_Usuario_Orfao3

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

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

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.

SQL_Server_Usuario_Orfao4

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

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 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 ?

Transact-SQL
1
2
3
4
5
6
USE [Testes]
EXEC sp_change_users_login
    'Update_One',
    'Usuario_Orfao',  -- Usuário
    'Usuario_Teste'   -- Login
GO

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

Transact-SQL
1
2
3
4
5
6
7
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

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:

Transact-SQL
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:

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- 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!

Tags: órfãosorphansp_change_users_loginsqlsql serverusersusuários

You may also like...

  • SQL Server – Consultando informações da instância no Windows Registry utilizando sys.dm_server_registry e xp_instance_regread

  • SQL Server – Como copiar/replicar as permissões de um usuário

  • SQL Server – Utilizando a traceflag 3226 para evitar a gravação de mensagens informativas geradas por backups no log do SQL Server

  • Next Semelhanças e Diferenças entre DELETE, TRUNCATE e DROP TABLE
  • Previous Arithmetic Overflow na view de catálogo sys.syscolumns do SQL Server 2008

Deixe uma resposta Cancelar resposta

Dirceu Resende © 2020. All Rights Reserved.