FixOrphanUsers_without Readonly_DB

  Declare @cnt1 int,@cnt2 int
  Declare @i int,@j int
  Declare @user varchar(50)
  Declare @sSQL varchar(200)
  declare @DBName varchar(500)
  /* Temporary Table to Hold all the Databses in the server*/
 
  Set nocount ON
 
  Select @DBName=NULL

  CREATE TABLE #tmplg1
  (
    Rown tinyint identity(1,1),
    DB varchar(500)
   )

  /* Temporary Table to Hold all the Orphaned users*/
  CREATE TABLE #tmplg2
  (
    Rown int identity(1,1),
    DBUser varchar(50)
   )
  /*Insert all the Databases into the Temp table If its not provided in the Parameter Else insert just the Specified database*/
  IF (Isnull(@DBName,'A')='A')
  Insert into #tmplg1(DB)
  select name from master.sys.databases where state_desc='ONLINE'and is_read_only != 1
    
  Else
  Insert into #tmplg1(DB) Values(@DBName)
 
  --Select * from #tmplg1

  Select @cnt1=count(1) from #tmplg1
  Select @i=1

  While (@i<=@cnt1)
  Begin
    Select @DBName=DB from #tmplg1 where Rown=@i
    Print char(13)+ 'Processing ' + @DBName + '...' + Char (13)
    SELECT @sSQL='Insert into #tmplg2(DBUser) '
    SELECT @sSQL= @sSQL + 'Select UserName = name  from [' + @DBName + ']..sysusers where issqluser = 1 and (sid is not null and sid <> 0x0) and suser_sname(sid) is null'
    Exec (@sSQL)

    Select @cnt2=count(1) from #tmplg2
    Select @j=1
    While(@j<=@cnt2)
    Begin
      Select @user=DBUser from #tmplg2 where Rown=@j
      IF EXISTS(select * from master..syslogins where loginname = @user) 
      Begin
        SELECT @sSQL='Use ['+@DBName+'] EXEC sp_change_users_login "Update_One","'+ @user+ '","'+ @user + '"'
        Exec (@sSQL)
--       Print @sSQL
      End
      Else
      Begin
        Print 'User '+ @user + ' is not present in SYSLOGIN'
      End
    --Select @j
    Select @j=@j+1
    End
  Select @i=@i+1
  End

/*Drops the Temporary tables*/
  Drop table #tmplg1
  Drop table #tmplg2