Script to find the Objects Owned by Orphaned Users

Script to find the Objects Owned by Orphaned Users

  • Comments 1

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.

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

 

-----Start of Script-----

 

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

 

-----End of script-----

 

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

Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
  • Needed it badly. Many thanks for good work

Page 1 of 1 (1 items)