Czy istnieje skrótowy sposób „Auto_Fix” wszystkich osieroconych użytkowników w bazie danych SQL Server 2008 R2?

Naprawienie pojedynczego osieroconego użytkownika SQL do logowania przy użyciu:

EXEC sp_change_users_login „Auto_Fix”, „użytkownik”

Mógłbym to zrobić, ale czy istnieje już procedura składowana, która automatycznie próbuje naprawić każdego osieroconego użytkownika w danej bazie danych?

Ted Krueger ( @onpnt na Twitterze) napisał świetny skrypt, który to robi. Dodaje loginy każdemu użytkownikowi bez loginu i uruchamia auto_fix. Napisał nawet taki, który obejmuje naprawianie logowania do systemu Windows:

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

Oczywiście, jeśli chcesz go najpierw przetestować (lub po prostu przeprowadzić audyt), możesz skomentować linie akcji ( EXEC) i po prostu wydrukować wyniki.

Poniżej znajduje się prosty skrypt, który doskonale wykonuje zadanie -

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

bazując na powyższym skrypcie, możemy naprawić wszystkich użytkowników w instancji, używając sp_MSForeachdb w ten sposób

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

mam nadzieję, że to pomoże

Byłby to świetny przypadek użycia komendy dbatools Repair-DbaDbOrphanUser

Najpierw możesz zidentyfikować osieroconych użytkowników

Get-DbaDbOrphanUser -SqlInstance $sqlinstance

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

a następnie rozwiąż je za pomocą

Repair-DbaDbOrphanUser -SqlInstance $sqlinstance

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