Advertisements

SQL-BOX.COM

–SQL Server Storage

Auto Fix orphan users on all Databases

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(39) 
                            from sysusers 
                            where issqluser = 1 and (sid is not null and sid <> 0x0AND 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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: