Auto Fix orphan users on all Databases

Posted by
DECLARE @DB_Name varchar(100)  
DECLARE @Command nvarchar(2000DECLARE database_cursor CURSOR FOR  
SELECT name 
FROM  sys.databases where database_id>4 
and name not like '%master%' 
 
OPEN database_cursor 
 
FETCH NEXT FROM database_cursor INTO @DB_Name 
 
WHILE @@FETCH_STATUS = 0  
BEGIN  
                          
SELECT @Command ='  
            use '+ @DB_Name+';  
            declare @query varchar(1000) 
            declare @executequery cursor 
            set @executequery=cursor for 
select '' sp_change_users_login ''+CHAR(39)+''update_one''+
CHAR(39)+'',''+CHAR(39)+name+CHAR(39)+'',
''+CHAR(39)+name+CHAR(39from sysusers 
where issqluser = 1 and (sid is not null and sid <> 0x0
AND SUSER_SNAME(sidIS NULL 
open @executequery 
fetch next from @executequery into @query 
                            while @@fetch_status=0 
                            begin  
                             exec (@query) 
                             print (@query) 
         fetch next from @executequery into @query 
                            end 
                            close @executequery; 
                            deallocate @executequery; 
                            go' 
                             
                            print @Command 
                           
  FETCH NEXT FROM database_cursor INTO @DB_Name  
END 
 
CLOSE database_cursor  
DEALLOCATE database_cursor

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s