Wednesday, February 4, 2009

Verifying logins integrity between SQL 2005 servers with PowerShell.

Hi,
Recently I had to verify that every login in my production servers were in my backup servers, so I did a script using PowerShell to do that. This script is useful if you want to make sure that all your backup servers have the production logins.

The script takes 2 parameters, the production server and the backup server, all the logins in the production server must be in the backup server, in case it finds someone is missing, it writes it in a log and displays the login in the console.

This is the script:
param (
[string]$ServerOp ,
[string]$ServerBackup)

$LogFile = "C:\Intlogins\logs\Int_LOGINS_$nombre.log"

out-file -filepath $LogFile -inputobject ("#############################################################################################################################################")
out-file -filepath $LogFile -inputobject (" ") -append
out-file -filepath $LogFile -inputobject " START PROCESS TO VERIFY LOGINS INTEGRITY " -append
out-file -filepath $LogFile -inputobject (" ") -append
out-file -filepath $LogFile -inputobject ("#############################################################################################################################################") -append
out-file -filepath $LogFile -inputobject (" ") -append

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$serverOp;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from master.sys.server_principals where is_disabled=0 and principal_id>258 and name <>'##MS_AgentSigningCertificate##'"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object "System.Data.DataSet" "Table1"
$result = $SqlAdapter.fill($DataSet)

$SqlConnection2 = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection2.ConnectionString = "Server=$serverBackup;Database=master;Integrated Security=True"
$SqlCmd2 = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd2.CommandText = "select name from master.sys.server_principals where is_disabled=0 and principal_id>258 and name <>'##MS_AgentSigningCertificate##'"
$SqlCmd2.Connection = $SqlConnection2
$SqlAdapter2 = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter2.SelectCommand = $SqlCmd2
$DataSet2 = New-Object "System.Data.DataSet" "Table1"
$result2 = $SqlAdapter2.fill($DataSet2)

foreach ($row in $dataset.Tables[0].rows){
$exist=0
foreach ($row2 in $Dataset2.Tables[0].rows){
if([string]::Compare($row2.name,$row.name,$True) -eq 0){
# Write-Host "The login" $row.name "exists in backup server"
$exist=1
break
}
}
if ($exist -eq 0){
Write-Host "The login" $row.name "does NOT exist in backup server"
$var = "$hora ERROR: The login ["+ $row.name+ "] does NOT exist in backup server: $ServerBackup"
out-file -filepath $LogFile -inputobject $var -append

}
}

out-file -filepath $LogFile -inputobject (" ") -append
out-file -filepath $LogFile -inputobject (" ") -append
out-file -filepath $LogFile -inputobject ("#############################################################################################################################################") -append
out-file -filepath $LogFile -inputobject (" ") -append
out-file -filepath $LogFile -inputobject (" END PROCESS TO VERIFY LOGINS INTEGRITY") -append
out-file -filepath $LogFile -inputobject (" ") -append
out-file -filepath $LogFile -inputobject ("#############################################################################################################################################") -append

No comments: