Ajustando Usuários Órfãos – SQL Server

Vamos falar um pouco sobre um assunto que as vezes incomoda que são Usuários Órfãos ou como encontramos nas documentações da Microsoft, os Orphaned Users.

Abaixo segue um código para identificar os usuários da Database do contexto atual e para
qual Login do SQL Server este usuário está associado. Nos casos onde ServerLogin estiver como NULL temos um usuário Órfão.
No caso de Logins do Tipo SQL Server devemos utilizar os procedimentos descritos mais abaixo, no post, mas para caso de usuario do tipo Windows authentication basta criarmos o Login no Servidor.


SELECT usu.name as DBUser,lo.loginname as ServerLogin
FROM DB.dbo.sysusers usu
LEFT OUTER JOIN master.dbo.syslogins lo
ON usu.sid = lo.sid
WHERE usu.islogin = 1
AND usu.isaliased = 0
AND usu.hasdbaccess = 1

Geralmente encontramos este tipo de problema em migrações de Banco de dados como por exemplo, criação de uma base de desenvolvimento em outro servidor/instância.

O código abaixo mostra como identificar e associar um usuário órfão em uma database:



--identifica os usuarios órfãos
exec dbo.sp_change_users_login 'report'

--associa o usuário com seu respectivo login
exec sp_change_users_login 'Update_One','usr_Teste', 'usr_Teste'


Abaixo segue uma Stored Procedure que fiz para resolver este problema de maneira automática para facilitar a vida quando criamos, por exemplo, Jobs para atualização de ambientes de desenvolvimento.


createprocedure sp_ajusta_logins  @db varchar(30) = NULL
as
SET NOCOUNT ON
DECLARE @sql 	nvarchar(1000)
DECLARE @User	sysname
--Temp com os usuarios
CREATE TABLE #tbUsuarios (usuarios sysname,id BIGINT)
IF @db is null
begin
 print 'Por segurança, o parametro DataBase é obrigatório. Para executar para todos os DBs utilize sp_ajusta_logins ''TODOS'' ou sp_ajusta_logins ''nome_da_base'''
end else
IF not(upper(Rtrim(lTrim(@db))) = 'TODOS') -- Para um db específico
BEGIN
	PRINT 'Associando usuários órfãos para o DataBase '+@Db+' - '+convert(varchar,getdate())
	SET @sql = @db+'.dbo.sp_change_users_login ''report'''
	INSERT INTO #tbUsuarios exec sp_executesql @sql	
	
	IF exists(SELECT usuarios FROM #tbUsuarios)
	BEGIN
		SELECT @User = min(usuarios) from #tbUsuarios
		WHILE @User is not null
		BEGIN
			SELECT @sql = @db+'.dbo.sp_change_users_login ''Update_One'','''+ @User + ''','''+ @User +''''
			begin try
				EXEC sp_executesql @sql
				SET @sql = 'O usuário '''+ @User +''' do database '''+@db +''' foi associado ao seu login '''+@User+''''
				Print @sql
			end try
			begin catch
				SET @sql = N'Ocorreu um erro ao atualizar o usuário '''+ @User +''' do database '''+@db+''' - '+ERROR_MESSAGE()
				RAISERROR (@sql , 10, 1);
			end catch
			SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User			
		END
	END
END
ELSE
BEGIN
	print 'Executando SP para todos os DBS
	'
	-- Pesquisa em todos os dbs
	SELECT @db = min(name) from master.dbo.sysdatabases where name not in ('tempdb', 'pubs', 'msdb', 'NorthWind', 'master','model')
	PRINT 'Associando usuários órfãos para o DataBase '+@Db+' - '+convert(varchar,getdate())
	WHILE @db is not null
	BEGIN
		SET @sql = @db+'.dbo.sp_change_users_login ''report'''

		INSERT INTO	#tbUsuarios exec sp_executesql @sql
		IF exists(SELECT usuarios FROM #tbUsuarios)
		BEGIN
			SELECT @User = min(usuarios) from #tbUsuarios
			WHILE @User is not null
			BEGIN
				SET @sql = @db+'..sp_change_users_login ''Update_One'','''+ @User + ''','''+ @User +''''
				begin try
					EXEC sp_executesql @sql
					SET @sql = 'O usuário '''+ @User +''' do database '''+@db +''' foi associado ao seu login '''+@User+''''
					Print @sql
				end try
				begin catch
					SET @sql = N'Ocorreu um erro ao atualizar o usuário '''+ @User +''' do database '''+@db+''' - '+ERROR_MESSAGE()
					RAISERROR (@sql , 10, 1);
				end catch				
				SELECT @User = min(usuarios) from #tbUsuarios where usuarios > @User				
			END
		END
		DELETE #tbUsuarios
		SELECT @db = min(name) FROM master.dbo.sysdatabases WHERE name not in ('tempdb', 'pubs', 'msdb', 'NorthWind', 'master','model')
		AND Name > @db
	END
END

DROP TABLE #tbUsuarios
print 'Processo Concluído!'

Abraço!

Leave a Reply

Your email address will not be published. Required fields are marked *