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:
Find Orphaned Database Users Script:
Create Table #Orphans
RowID int not null primary key identity(1,1) ,
TDBName varchar (100),
UserName varchar (100),
SET NOCOUNT ON
DECLARE @DBName sysname, @Qry nvarchar(4000)
SET @Qry = ''
SET @DBName = ''
WHILE @DBName IS NOT NULL
SET @DBName =
/** to exclude named databases add them to the Not In clause **/
name NOT IN
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)
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
While @From < @To
Set @From = @From + 1
Select @DDBName = TDBName, @Orphanname = UserName from #Orphans
Where RowID = @From
Set @DBSysSchema = '[' + @DDBName + ']' + '.[sys].[schemas]'
set @SQL = 'If Exists (Select * from ' + @DBSysSchema
+ ' where name = ''' + @Orphanname + ''')
Use ' + @DDBName
+ ' Drop Schema [' + @Orphanname + ']
Set @SQL = 'Use ' + @DDBName
+ ' Drop User [' + @Orphanname + ']'
Drop table #Orphans