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:
| 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:
| 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:
Agora, o que acontece com o usuário se excluirmos o seu login ?
| 1 2 3 | USE [master] DROP LOGIN [Usuario_Teste] GO |
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?
| 1 2 | USE [Testes] EXEC sp_change_users_login 'Report' |
Ou utilizando a sys.syslogins (SQL Server 2000 em diante):
| 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):
| 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 |
Já recriei o login. Como faço para recriar a associação Login x Usuário ?
| 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.
Recriando o login de todos os usuários, de todos os databases, da sua instância
| 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 ?
| 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.
| 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:
| 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:
| 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!
