How to identify Orphaned Logins and Objects owned by Oraphaned Logins

  • Article

 

Below is the script that would first identify and display Orphaned users. Then this output is passed to the next level which identifies the objects owned by this User in all the databases

I have implemented temporary tables hence the entire script should be executed every time.

You can convert them to tables and script them to a Stored Procedure.

====================================================================

Set Nocount on

Declare @OrphLogins Table (SID Varchar(200), NTlogin Varchar(200))

 -- Inserting the orphaned NT user into temp table

Insert into @OrphLogins EXEC Sp_ValidateLogins

 --Display the number of Orphaned Users

Select NTLogin As "Orphaned Logins" From @OrphLogins

DECLARE @Login varchar(200)

DECLARE Orphcursor CURSOR FOR

SELECT NTLogin from @OrphLogins

OPEN OrphCursor

FETCH NEXT FROM OrphCursor INTO @Login

WHILE @@FETCH_STATUS = 0

BEGIN

Declare @TSequel Varchar(MAX), @DatabaseO Varchar(MAX)

    Select @DatabaseO = ' SrPri.name COLLATE DATABASE_DEFAULT as Login, DbPri.Name COLLATE DATABASE_DEFAULT as [User],

 orph.name COLLATE DATABASE_DEFAULT As [Name],

 orph.type_desc COLLATE DATABASE_DEFAULT As [Object Type]

 From %D%.sys.objects orph

    Join %D%.sys.database_principals DbPri ON Coalesce(orph.principal_id,

 (Select Sch.Principal_ID From %D%.sys.schemas Sch Where Sch.Schema_ID = orph.schema_id)) = DbPri.principal_id

    Left Join %D%.sys.server_principals SrPri On SrPri.sid = DbPri.sid '

    Select @TSequel = 'SELECT * FROM

    (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '

                     + Replace(@DatabaseO, '%D%', [name])

    From master.sys.databases

    Where [name] = 'master'

    Select @TSequel = @TSequel + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '

                     + Replace(@DatabaseO, '%D%', [name])

    From master.sys.databases

    Where [name] != 'master'

    Select @TSequel = @TSequel + ') LL Where Login = ''' + @Login + ''''

    --print @sql

    EXEC (@TSequel)

  

   FETCH NEXT FROM OrphCursor

   INTO @Login

END

CLOSE OrphCursor

DEALLOCATE OrphCursor

GO

Example Output

==========================

Orphaned Logins

--------------------

Domain\deluser2

LocalMachine\deluser3

DBID DBName Login User Name Object Type

----- ------------- --------------------- --------------------- -------------------- ---------------------

16 Test LocalMachine \deluser3 LocalMachine\deluser3 Table1 USER_TABLE

16 Test LocalMachine \deluser3 LocalMachine\deluser3 View1 VIEW

16 Test LocalMachine \deluser3 LocalMachine\deluser3 proc1 SQL_STORED_PROCEDURE

16 Test LocalMachine \deluser3 LocalMachine\deluser3 table2 USER_TABLE

16 Test LocalMachine \deluser3 LocalMachine \deluser3 spLogin_OwnedObjects SQL_STORED_PROCEDURE

30 TransPublish LocalMachine \deluser3 LocalMachine \deluser3 Table10 USER_TABLE

 

 

Levi Justus
Technical Lead, Microsoft Sql Server