Orphaned Users

Eric Johnson wrote up a great article about orphaned users.  Any DBA has to deal with this when refreshing databases from one environment to another (e.g. prod to stage, or prod to dev).  If you have a large number of users to repair, the built-in proc sucks because you are doing them one at a time.

Over 6 years ago I wrote this SQL to automate the process because like any good (meaning lazy) developer, I don’t like doing mundane things repeatedly.  I also did this because, like Eric, I don’t want a script auto-magically creating logins for me.  So this SQL simply loops over the orphaned users and tries to repair them if the matching login exists.  If it doesn’t it will generate a script that you can execute to create the missing login(s).

NOTE: This only works if the database users match the SQL logins, which was the case when I wrote it.

TIPS:

1. Set your results to text instead of grid when using this.
2. This executes in whatever database you are connected to.

declare @ExecString nvarchar(255),
        @Name nvarchar(50),
        @Count int,
        @FixString varchar(8000),
        @CurrentDB varchar(50),
        @OrphanedCount int

set nocount on

set @Count = 0

set @CurrentDB = DB_NAME()

select @OrphanedCount = count(*)
  from sysusers
 where issqluser = 1
   and (sid is not null and sid <> 0x0)
   and suser_sname(sid) is null

if @OrphanedCount > 0
   begin
      declare CURS cursor for
       select name
         from sysusers
        where issqluser = 1
          and (sid is not null and sid <> 0x0)
          and suser_sname(sid) is null
        order by name
      open CURS

      fetch next from CURS into @Name
      select ' ' as 'Attempting Repairs'

      while @@FETCH_STATUS = 0
      begin
         print 'Attempting to repair "' + @Name + '"'
         if exists(select * from master..syslogins where name = @Name)
            begin
               set @ExecString = N'exec sp_change_users_login ''update_one'',''' + @Name + N''',''' + @Name + N''''
               exec sp_executesql @ExecString
               print '   "' + @Name + '" successfully repaired...'
            end
         else
            begin
               print '   "' + @Name + '" does not have a matching entry in syslogins...'
               set @FixString = isnull(@FixString,'') + 'exec sp_addlogin ''' + @Name + ''', ''{password}'', ''' +
                                @CurrentDB + '''' + char(13) + char(10) + 'GO' + char(13) + char(10)
               set @Count = @Count + 1
            end

         fetch next from curs into @Name
      end
      print ' '

      close CURS
      deallocate CURS

      if @Count > 0
         begin
            print ' '
            select convert(varchar(5),@Count) +
                   ' user(s) could not be repaired because they do not have a matching entry in syslogins' as Results
            print ' '
            select @FixString as 'Script to Fix Users (copy/paste/edit to add users ... then execute this script again!)'
         end
      else
         begin
            print ' '
            select 'Successfully repaired all users' as Results
         end
   end
else
   begin
      print ' '
      select 'No orphaned users exist in database "' + @CurrentDB + '".' as Results
   end

set nocount off

GO

I wrote this (hopefully obvious to you based on it being 6 years ago) against SQL Server 2000.  I am posting it to help out, but I haven’t used it much against SQL 2005/2008.  I did a few tests and it still seems to work as expected.

Stay tuned, I will write an enhanced version of this script in the next week using a more contemporary style.