0 Replies Latest reply: Aug 20, 2013 8:57 PM by 799327 RSS

    Search user in All SQL Servers & Oracle DBs in 1-shot

    799327

      For SQL server, Query running fine. We have linked server to connect with Oracle servers. Want to check e.g. "MMASON" user in all oracle DB's while running below query in SQL server.

       

      SET NOCOUNT ON

      SET DATEFORMAT ymd

      DECLARE @login VARCHAR(99)

      SET @login = 'MMASON'  -- ***UPDATE WITH USER DEPARTING***

       

      DECLARE @server VARCHAR(99)

      DECLARE @query_txt VARCHAR(6500)

          -- Create list of server to check for job status

      DECLARE servers CURSOR FOR

        SELECT DISTINCT srvname

        FROM master.dbo.sysservers

        WHERE (isremote = 0)

         AND (srvproduct in ('Oracle 10gR2','Oracle 11g R2'))

         AND (srvname NOT LIKE 'LT-%')

         AND (srvname NOT LIKE 'DT-%')

        UNION

        SELECT @@SERVERNAME AS srvname

      OPEN servers

      FETCH NEXT FROM servers INTO @server

      WHILE @@fetch_status = 0

      BEGIN

           -- Query job system tables for any jobs that are

           --  enabled and that failed on last execution

        SELECT @query_txt = '

      SELECT                                 -- ***Trying to modify this section to search user in Oracle***

      ''' + @server + ''' AS ServerName,

      sys.dba_users.[username] AS LoginName

      FROM [' + @server + '].sys.dba_users sl

      WHERE sl.[username] LIKE ''' + @login + '''

      '

        --PRINT @query_txt

        EXEC(@query_txt)

        FETCH NEXT FROM servers INTO @server

      END

      CLOSE servers

      DEALLOCATE servers

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

       

      This query search through Linked Servers in SQL Server. But, don't know how to pass @login parameter in Oracle to search specified user (exist/not exist on Oracle Servers). Otherwise, you have to check manually in each database, which is time consuming task.

       

      Please advise.