Tip: “sp_validatelogins” and “sp_change_users_login”(Orphaned Users in SQL Server)

dbtut

6 years ago

Detect Orphaned Windows Logins(sp_validatelogins)

With this system SP, you can identify Windows Logins that have been deleted from Active Directory on a SQL Server Instance.

You can run SP under the “master” database, if you have a Windows Login deleted from Active Directory, then this SP returns a result as follows:

SID NT Login
0x010500000000000515000000AE78308265B37A613F005BB0A00C0000 CONTOSO\ONSOYEK

1 2	SID NT Login 0x010500000000000515000000AE78308265B37A613F005BB0A00C0000 CONTOSO\ONSOYEK

This Login is not linked to any Active Directory user, so it does not work any good, so it can be easily deleted.

For more information about this SP, read Books Online:http://msdn.microsoft.com/en-us/library/ms181728.aspx

You can also compare this SP to the “sp_change_users_login” system SP. With sp_change_users_login, you can also identify orphaned SQL Users at the database level and map them to their Login.

What is Orphaned Users in SQL Server

If you create a login and map this login to a database, sql server creates a user in that database for mapping loggins and users. And when you delete the login mapped to that database, sql server doesnt delete the user in the database. So this user in the database no longer related to with a login in the instance. its orphaned now.

Find Orphaned Users in SQL Server(sp_change_users_login)

To detect orphaned SQL users in a database, you can run a command as follows:

USE database_name
GO
EXEC sp_change_users_login 'report'
GO

USE database_name

EXEC sp_change_users_login 'report'

If a result returns, then this means that there are orphaned SQL Users in that database.

sp_change_users_login is not working

Since sp_change_users_login is depreceated, it may not return any result for your system. You can use below script. I stole this script from below link.

https://www.mssqltips.com/sqlservertip/2624/script-to-find-and-drop-all-orphaned-users-in-all-sql-server-databases/

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 orphans uncomment this section 
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

Use master

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 orphans uncomment this section

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

If you dont want to deleted orphaned users and match a login with the orphaned user you should use below script. I suggest using ALTER statement instead of sp_change_users_login due to it is depreceated.

USE database_name
GO
EXEC sp_change_users_login 'Auto_Fix', 'user_name'
GO

USE database_name

EXEC sp_change_users_login 'Auto_Fix', 'user_name'

In SQL Server 2008 and later versions, you can do the same with the following command:

ALTER USER test WITH LOGIN = test

1	ALTER USER test WITH LOGIN = test

In summary, sp_validateusers is used to detect orphaned Windows Logins; sp_change_users_login can also be used to identify orphaned SQL Users.