Is there a shorthand way to 'Auto_Fix' all Orphaned Users in an SQL Server 2008 R2 database?
Ted Krueger (@onpnt on twitter) wrote a great script that does this. It adds logins for any user without a login and runs the auto_fix. He even wrote one that includes fixing Windows logins:
http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/fixing-orphaned-database-users
Of course if you want to test it first (or merely perform an audit) you can comment out the action lines (EXEC
) and just print out the results.
base on above script, we can fix all user at an instance by using sp_MSForeachdb like this
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
hope it'll help
Below is the simple script that does the job perfectly --
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