Thursday, 29 August 2013

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
Create Table #Orphans
  RowID     int not null primary key identity(1,1) ,
  TDBName varchar (100),
  UserName varchar (100),
  UserSid varbinary(85)
 DECLARE @DBName sysname, @Qry nvarchar(4000)
 SET @Qry = ''
 SET @DBName = ''
   SET @DBName =
  SELECT MIN(name)
   FROM master..sysdatabases
   /** to exclude named databases add them to the Not In clause **/
   name NOT IN
      'model', 'msdb',
     ) And
     DATABASEPROPERTY(name, 'IsOffline') = 0
     AND DATABASEPROPERTY(name, 'IsSuspect') = 0
     AND name > @DBName
                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)

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
  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 + ''')
     Use ' + @DDBName
                                        + ' Drop Schema [' + @Orphanname + ']
   print @SQL
   Exec (@SQL)
    Begin Try
     Set @SQL = 'Use ' + @DDBName
                                        + ' Drop User [' + @Orphanname + ']'
     Exec (@SQL)
    End Try
    Begin Catch
    End Catch

Drop table #Orphans