locked
Need to run sp_validatelogins against multiple SQL instances RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote 
    $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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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