Find and Drop All Orphaned Users in All SQL Server Databases Script

What Is An Orphaned SQL User:

An orphaned user is a database user that does not have an associated SQL login. There are a number of ways a user can become orphaned. The most common way is when a database from a different server is restored. When a database is backed up and restored the database users are also backed up and restored with the database, but the SQL login is not. If a database is restored to the same server the backup came from and the logins already exist then the database users will not be orphaned because security identifiers (SID) will be the same. If a database is restored to a different server and there are logins with the same name chances are the database users will be orphaned because the identifiers are not the same. And if the logins do not exist at all the database users will be orphaned. Another way database users can be orphaned is if the SQL login is deleted without checking for database users.

Find Orphans In All Databases T-SQL Command:

use DB_name;

sp_change_users_login @Action='Report';

Find Orphaned Database Users Script:

Use master
Go
Create Table #Orphans
 (
  RowID     int not null primary key identity(1,1) ,
  TDBName varchar (100),
  UserName varchar (100),
  UserSid varbinary(85)
 )
SET NOCOUNT ON
 DECLARE @DBName sysname, @Qry nvarchar(4000)
 SET @Qry = ''
 SET @DBName = ''
 WHILE @DBName IS NOT NULL
 BEGIN
   SET @DBName =
     (
  SELECT MIN(name)
   FROM master..sysdatabases
   WHERE
   /** to exclude named databases add them to the Not In clause **/
   name NOT IN
     (
      'model', 'msdb',
      'distribution'
     ) And
     DATABASEPROPERTY(name, 'IsOffline') = 0
     AND DATABASEPROPERTY(name, 'IsSuspect') = 0
     AND name > @DBName
      )
   IF @DBName IS NULL BREAK
       
                Set @Qry = 'select ''' + @DBName + ''' as DBName, name AS UserName,
                sid AS UserSID from [' + @DBName + ']..sysusers
                where issqluser = 1 and (sid is not null and sid <> 0x0)
                and suser_sname(sid) is null order by name'
 Insert into #Orphans Exec (@Qry)

 End
Select * from #Orphans


---To Drop Orphan User:

Declare @SQL as varchar (200)
Declare @DDBName varchar (100)
Declare @Orphanname varchar (100)
Declare @DBSysSchema varchar (100)
Declare @From int
Declare @To int
Select @From = 0, @To = @@ROWCOUNT
from #Orphans
--Print @From
--Print @To
While @From < @To
 Begin
  Set @From = @From + 1

  Select @DDBName = TDBName, @Orphanname = UserName from #Orphans
   Where RowID = @From
   
   Set @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'
   print @DBsysSchema
   Print @DDBname
   Print @Orphanname
   set @SQL = 'If Exists (Select * from ' + @DBSysSchema
                          + ' where name = ''' + @Orphanname + ''')
    Begin
     Use ' + @DDBName
                                        + ' Drop Schema [' + @Orphanname + ']
    End'
   print @SQL
   Exec (@SQL)
   
    Begin Try
     Set @SQL = 'Use ' + @DDBName
                                        + ' Drop User [' + @Orphanname + ']'
     Exec (@SQL)
    End Try
    Begin Catch
    End Catch
 
 End


Drop table #Orphans