Início / dba / Perguntas / 12817
Accepted
Daniel James Bryars
Asked: 2012-02-14 07:31:35 +0800 CST

Existe uma maneira abreviada de 'Auto_Fix' todos os usuários órfãos em um banco de dados SQL Server 2008 R2?

  • 772

É bastante simples corrigir um único usuário SQL órfão para um login usando:

EXEC sp_change_users_login 'Auto_Fix', 'usuário'

Eu poderia fazer um script disso, mas existe um procedimento armazenado existente que tenta automaticamente corrigir todos os usuários órfãos em um determinado banco de dados?

sql-server-2008-r2

6 respostas

  1. Best Answer

    Ted Krueger ( @onpnt no twitter) escreveu um ótimo script que faz isso. Ele adiciona logins para qualquer usuário sem login e executa o auto_fix. Ele até escreveu um que inclui a correção de logins do Windows:

    http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users

    Claro que se você quiser testá-lo primeiro (ou simplesmente realizar uma auditoria) você pode comentar as linhas de ação ( EXEC) e apenas imprimir os resultados.

    • 16

  2. com base no script acima, podemos corrigir todos os usuários em uma instância usando sp_MSForeachdb assim

    declare @name varchar(150)
declare @query nvarchar (500)

DECLARE cur CURSOR FOR
    select name from master..syslogins

Open cur

FETCH NEXT FROM cur into @name

WHILE @@FETCH_STATUS = 0
BEGIN

set @query='USE [?]
IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb''
BEGIN   
exec sp_change_users_login ''Auto_Fix'', '''+ @name +'''
END'

EXEC master..sp_MSForeachdb @query

    FETCH NEXT FROM cur into @name

END

CLOSE cur
DEALLOCATE cur

    espero que ajude

    • 6

  3. Abaixo está o script simples que faz o trabalho perfeitamente -

    USE DBNAME     ----- change db name for which you waant to fix orphan users issue

GO


declare @name varchar(150)

DECLARE cur CURSOR FOR
    select name from master..syslogins

Open cur

FETCH NEXT FROM cur into @name

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC sp_change_users_login 'AUTO_FIX', @name

    FETCH NEXT FROM cur into @name

END

CLOSE cur
DEALLOCATE cur
    • 2

  4. Este seria um ótimo caso de uso para usar o comando dbatools Repair-DbaDbOrphanUser

    Primeiro você pode identificar os usuários órfãos

    Get-DbaDbOrphanUser -SqlInstance $sqlinstance

ComputerName : SQL01
InstanceName : MSSQLSERVER
SqlInstance  : SQL01
DatabaseName : SockFactoryApp
User         : SockFactoryApp_User

    e, em seguida, resolvê-los com

    Repair-DbaDbOrphanUser -SqlInstance $sqlinstance

ComputerName : SQL01
InstanceName : MSSQLSERVER
SqlInstance  : SQL01
DatabaseName : SockFactoryApp
User         : SockFactoryApp_User
Status       : Success
    • 1

  5. Para corrigir usuários órfãos de um único banco de dados para todos os usuários

    BEGIN 

DECLARE @username varchar(25) 

DECLARE fixusers CURSOR 
FOR 

SELECT UserName = name FROM sysusers 
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) 
and suser_sname(sid) is null 
ORDER BY name 

OPEN fixusers 

FETCH NEXT FROM fixusers 
INTO @username 

WHILE @@FETCH_STATUS = 0 
BEGIN 
EXEC sp_change_users_login 'update_one', @username, @username 
FETCH NEXT FROM fixusers 
INTO @username 
END 


CLOSE fixusers 

DEALLOCATE fixusers 

END 

go
    • 0 
  6. EXEC sp_MSforeachdb 'USE ? exec sp_change_users_login ''Auto_Fix'', ''Username'';'
    • -2

relate perguntas