Skip to main content

Execute Guardium SQL Server Permissions Script on Multiple Servers


# SQLSaturday#85 in Orlando is September 24, 2011
# I'll be presenting "PowerShell by Example"
# Here's a typical example we will review.
#
# Want more?  Come to my session at 11 AM.
#
# Need more info on SQL Saturday Orlando, go here.
#
# Set permissions on multiple servers for GUARDIUM Vulnerability Assessment 
# Pre-requisites:
# PowerShell V1
# SQLPSX 1.6.1
# add-pssnapin sqlserverprovidersnapin100
# add-pssnapin sqlservercmdletsnapin100

1:
$serverName = "SQL999"
   2:  $databaseName = "InventoryDB"
   3:   
   4:  $FilePath = "C:\GUARDIUM\DataSources\Output"
   5:  $OutFile = Join-Path -path $FilePath -childPath ("MSSQL_GDM_Output" + (get-date).toString('yyyyMMdd_hhmmtt') + ".txt")
   6:   
   7:  # here string contains SQL query
   8:  $qry = @"
   9:  SELECT  DISTINCT
  10:          ISNULL(A.MACH_LPAR_NM, '') + CASE a.INST_NM
  11:                                         WHEN 'DEFAULT' THEN ''
  12:                                         ELSE '\'
  13:                                       END + CASE a.INST_NM
  14:                                               WHEN 'DEFAULT' THEN ''
  15:                                               ELSE ISNULL(a.INST_NM, '')
  16:                                             END AS InstanceName
  17:  FROM    InventoryDatabase
  23:   "@
  24:   
  25:  $Servers = Get-SqlData $serverName $databaseName $qry
  26:   
  27:  Start-Transcript $OutFile
  28:   
  29:  $ScriptToExecute =  "C:\GUARDIUM\Scripts\gdmmonitor-mss.sql"
  30:   
  31:  foreach ($svr in $Servers)
  32:  {
  33:      $serverName = $svr.InstanceName
  34:      Write-Host $serverName
  35:      trap {"Oops! It failed. $_"; continue } Invoke-Sqlcmd -InputFile $ScriptToExecute  -verbose -ServerInstance $serverName
  36:  }
  37:   
  38:  Stop-Transcript

Comments

Popular posts from this blog

Modifying Endpoint URLs on Availability Group Replicas

I recently had to modify the Endpoint URLs on our SQL Server Availability Group replicas.  The reason for this blog post is that I could not answer the following questions: Do I need to suspend data movement prior to making this change?  Would this change require a restart of the database instance? I spent enough time searching on my own to no avail that I tossed the question to the #sqlhelp hashtag on Twitter and Slack but didn't get an answer prior to executing the change request. After reading the relevant documentation, I think it's probably a good idea to suspend data movement for this change. The T-SQL is straightforward.  USE MASTER GO ALTER AVAILABILITY GROUP [AG1]  MODIFY REPLICA ON 'SQL2012-1' WITH (ENDPOINT_URL = 'TCP://10.10.10.1:5022'); ALTER AVAILABILITY GROUP [AG1]  MODIFY REPLICA ON 'SQL2012-2' WITH (ENDPOINT_URL = 'TCP://10.10.10.2:5022'); ALTER AVAILABILITY GROUP [AG2]  MODIFY REPLICA ON 'SQL2012-1

PASS Summit 2012 - Gone to the mountain and returned wiser

http://t.co/pmhsJ3rr I began my conference schedule by attending Allen White's pre-con "Automating SQL Server with PowerShell". Allen starts by telling everyone in attendance “We all can learn something from each other.  We all know something that someone else doesn't.” I thought this was a great intro and inspiration to the attendees to participate in the PASS Community. Later in the day while answering a question, Allen tells us he is not a PowerShell expert.  Which kind of surprises me.  He says he’s just figured out how to use PowerShell with SQL Server. I think he is being a bit too humble.   Afterwards, I talk to Allen about a script I’m working on and he points me in a direction that hopefully will help me finish it. All in all, it was e xcellent day of training on using PowerShell with SQL Server. As the main conference began, I tweeted about how tight the seating was in some of the rooms on the first day of the main conference.   After the Sum

PowerShell: Quick SQL Server Version Check

I have to keep track of our SQL Server version inventory.  The goal is to reduce the SQL Server 2000 population as fast as possible. The following PowerShell script will produce a csv file containing the database server name and the version of SQL Server it's running. 1: ## Get SQL Version installed on multiple servers ## 2: ## ./sqlver.ps1 3: $start = get-date 4: write-host "Start: " $start 5:   6: [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null 7:   8: $FilePath = "C:\Output" 9: $OutFile = Join-Path -path $FilePath -childPath ("SQLVersions_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".log") 10:   11: # Version inventory 12: @(foreach ($svr in get-content "C:\Input\AllLOBServers.txt") 13: { 14: $s = New-Object "Microsoft.SqlServer.Management.Smo.Server" $svr 15: $s | select Name, Version 16:   17: }) | export-csv -noType $OutFile 18:   1