Best way to snif multiple servers without the use of linked servers

  • I am trying to build an administration script for SS2K to find all instances where a specific integrated or SQL authenticated user name exists.  The problem I am having is that linkes servers doesn't give me the flexibiltiy I would like.  Do any of you have a script that can be exec either via iSQL or Query Analyzer?  This script is for use when employees seperate and have access to multiple systems (ex. QA and Production).  I know this can be done using ASP, or other development language, but my goal is to make this process a part of the SQL environment to avoid adding complexity for other users.

  • So, what kind of 'flexibility' are you looking for..? There are several 'problems' or 'tasks' within your general description of what you want to do.. (ie find users or find servers etc..)

    /Kenneth

  • Look at the SQL Farm Combine from SQL Farms. It lets you query multiple databases and servers at the same time, and does what you seek.

    Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

  • I like the sounds of the Combine app.  Too bad I need this sooner rather than later.

    Kenneth,

    To answer your question.  The flexibility I am looking for is that I would like to be able to run this script from ANY SQL Instance on my network and don't want to have to link all 35+ servers instances everytime I need to relocate the scripts execution server.  Long story on why this is necessary.  I ultimately need a script that will allow me to pass in USERNAME (Required and includes NT Authenticated usernames), SERVERNAME (Optional assumes %), and ShowObjects (True or False is true the output of the script would include a listing of all objects for each instance that the username is the owner).  The output of the script would be either to a table or to a txt file for printing.  I was envisioning a script that could be executed via iSQL or run as an Agent Job.

    Does that help?  The main portion I am in search of is T-SQL scripting to select criteria from a table and use that criteria to run a simple select statement on each instance returned in the criteria query.  Make sense?

  • Mmm yes think it makes sense. Don't have any packaged solution, though.. But some thoughts.. (I'm sure you're aware of all already...)

    Being able to run from any instance requires that any instance has network access to all other instances. This is a networking issue only. For the rest, I've seen this kind of stuff before, and the only way to get data out of the servers is to connect and ask some SQL.

    For cycling through each server, one way of doing it is to have all server/instance names in a textfile, write a .cmd file with a FOR(serverfile.txt) DO(call isql/osql and do SQL stuff on each) loop cycling through each and output to file. In essence a DOS batch

    This could then be scheduled as job. I would probably try this path myself if I had to come up with something quickly myself. Also, this assumes that you already know all server/instance names in advance.

    /Kenneth

  • Using the SQL-DMO object library you can programmatically loop through server and object collections to achieve the result you want.

  • Kenneth,

    that logic sounds perfect.  Now I just have to figure it all out as this is my first project of this nature for SQL.  Everything I have done prior has been via .NET or other dev languages.

    Now I will show just how much of a newbie I am to SQL Dev   How would I use the SQL-DMO object library to programmatically loop through the servers and collections?  Can it be developed within a "bat" file or does I have to develop the solution within a development language?

  • Just to add to the above:

    SQL Farm Combine will be available for Beta testers on Thursday.

    Please refer to last month's SQL Server Magazine or SQL Server Standard that came out a couple of days ago to check out some of the features.

    Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

  • Here is a VB example using SQLDMO to list the server groups and their registered servers (there are many other collections that may be looped through such as Logins, Databases, etc.):

    To use SQLDMO in your program you must set a reference to: Microsoft SQLDMO Object Library.

    Sub test()

    Dim svrgrp As SQLDMO.ServerGroup

    Dim svr As SQLDMO.RegisteredServer

    Dim Login As SQLDMO.Login

    Dim ssvr As SQLDMO.SQLServer

    For Each svrgrp In SQLDMO.ServerGroups

       Debug.Print svrgrp.Name

       For Each svr In svrgrp.RegisteredServers

          Debug.Print "  " & svr.Name

       Next svr

    Next svrgrp

    End Sub

  • Just as I was a fraid of.  To use SQL-DMO requires a programming language/compiler to build and execute.  I am looking for a solution that can run as either a BAT file without the need to compile an executable or a T-SQL script that can be executed via iSQL.

     

  • Here's a snippet of something old I had lying around for automating a 'batch-delivery-thingy' to a given set of servers that implements the 'loop from a list' idea. It shouldn't be too hard to use as a template for what you want to do.

    (the ': DOSEXIT' at the bottom should be w/o the space, but I couldn't figure out how to get rid of the smiley if there was no space..' ) 

    @echo off

    @if not "%OS%"=="Windows_NT" goto DOSEXIT

    REM *** comments about what this script does

    REM *** xcopies all files from sourcedir to remotedir

    REM *** serverlist.txt contains UNC paths to all servers

    REM *** after copy, sets read only on destination

    rem copy out files to serverlist

    for /f "tokens=1" %%A in (serverlist.txt) DO xcopy putFilesHere\*.* %%A\myDir\ /s /e /r /i

    rem set readonly flag

    for /f "tokens=1" %%A in (serverlist.txt) DO attrib +r /s %%A\myDir\myOtherSubdir\*.*

    goto :EOF

    : DOSEXIT

    echo This script requires Windows NT

    /Kenneth

  • You can save the following code as a VB-Script (.vbs), and execute it like a .BAT file.  Or, execute the code using DTS, or as a Scheduled Task.  You don't have to compile it into an executable.

    Dim svrgrp

    Dim svr

    Dim Login

    Dim ssvr

    Dim strOutPut

    dim sqldmoapp

    set sqldmoapp = CreateObject("SQLDMO.Application")

    Set svrgrp = CreateObject("SQLDMO.ServerGroup")

    Set svr = CreateObject("SQLDMO.RegisteredServer")

    Set Login = CreateObject("SQLDMO.Login")

    Set ssvr = CreateObject("SQLDMO.SQLServer")

    For Each svrgrp In sqldmoapp.ServerGroups

       strOutPut = strOutPut & svrgrp.Name & vbCrLf

       For Each svr In svrgrp.RegisteredServers

            strOutPut = strOutPut & "  " & svr.Name & vbCrLf

            ssvr.Connect svr.Name, [USERID], [PASSWORD]

            For Each Login In ssvr.logins

                 strOutPut = strOutPut & "    " & Login.Name & vbCrLf

            Next

            ssvr.Disconnect

       Next

    Next

    MsgBox (strOutPut)

  • It does not require a compiler!

    Simply, create the file with the .vbs extension and you are done

    Cheers,

     


    * Noel

  • [banging own head against wall] so quickly I forget about that magical dev language known as vbscript   [/banging own head against wall]

     

    Thanks for the reminder and sample syntax.  That will get me to where I wanted to be.  Thanks to all that have helped.

  • You can run that script under WSH without a development environment.  You dim the variables, but without the type (You have to do createobject), and comment out with a single quote the As ...

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply