locked
Need to run sp_validatelogins against multiple SQL instances RRS feed

  • Question

  • Hi all--I need to run the stored procedure "sp_validatelogins" against multiple SQL instances.  I know the basic technique to fill in data:

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
    		$SqlConnection.ConnectionString = "Server = $Server; Database = master; Integrated Security = True"
    		
    		## Build the SQL command
    		$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    		$SqlCmd.CommandText = 'exec sp_validatelogins'
    		$SqlCmd.Connection = $SqlConnection
    		
    		## Execute the query
    		$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    		$SqlAdapter.SelectCommand = $SqlCmd
    		
    		## Return the dataset
    		$DataSet = New-Object System.Data.DataSet
    		$SqlAdapter.Fill($DataSet)
    		$results += $DataSet.Tables[0]


    I'm trying to customize the output from this:

    SID             NT Login     
    ---             --------     
    {1, 5, 0, 0...} CORP\user1

    What would be the best method to create this output?

    ComputerName      NT Login

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

    server1                 CORP\user1

    server1                 CORP\user2

    ...



    Monday, November 27, 2017 6:35 PM

All replies

  • You will have to connect to all instances in a loop and one at a time.  Don't use an adapter.  Just use a DataTable and load it from a reader.  The loads will add to the table if you always use the same table.

    Your current example only connects to the default instance.

    If you use SMO discovery then the instance name will be part of the results.


    \_(ツ)_/

    Monday, November 27, 2017 8:04 PM
  • $conn = New-Object System.Data.SqlClient.SqlConnection
    $dt = New-Object System.Data.DataTable
    
    foreach($server in $servers){
        Try{
            $conn.ConnectionString = "Server=$Server;Database=master;Integrated Security=True"
            $conn.Open()
            $cmd = $conn.CreateCommand()
            $cmd.CommandText = 'exec sp_validatelogins'
            $rdr = $cmd.ExecuteReader()
            $dt.Load($rdr)
            $conn.Close()
        }
        Catch{
            Write-Host 'OOPS!'
        }
    }
    $dt


    \_(ツ)_/



    • Edited by jrv Monday, November 27, 2017 8:36 PM
    Monday, November 27, 2017 8:10 PM
  • This is how to upgrade a stored procedure to add the server name to the results:

    Declare @tablevar table(sidstr  varchar,account varchar)
    insert into @tablevar(sidstr,account) exec sp_validatelogins 
    
    SELECT @@SERVERNAME as ServerName,sidstr, account FROM @tablevar


    \_(ツ)_/

    Monday, November 27, 2017 8:33 PM