PowerShell –SQL Automate Tutorial-11-Find SQL services running Service Account for Audit Purpose

Disclaimer:Hey Guys, this post contains affiliate link to help our reader to buy best product\service for them. It helps us because we receive compensation for our time and expenses.

Scenario: PS is most powerful and very helpful tool for windows and SQL server administrator and automation. So its today need to learn at least basic of some PS cmdlets.

In this tutorial, we will walk you through getting SQL services running service account details using PowerShell across multiple servers.

Note:- Please click here to get 10th part of PowerShell –SQL Automate tutorial.

To get more information about PowerShell cmdlets, you can refer this link.

Being a DBA it could be in our day to day activities where management or for administrative purpose we may require to get some sort of SQL servers or DB related details across multiple servers of the environments.

PowerShell is very powerful tool for automating these stuffs hence its knowledge is MUST for DBA.

Lets check this out, how to get these SQL services running service account details using PowerShell across multiple servers.

Go through the code below,it has been explained in the details.

Download below code form Here

You can also refer few links below…

https://sql-box.com/2012/10/03/how-to-run-a-sql-query-on-list-of-servers-and-save-output-to-excel-spreadsheet-using-power-shell/

https://octopus.com/blog/sql-server-powershell

#Copy and paste required list of SQL server in TXT file of your desired directory and use Get-content cmdlet to read those servers.

$SQLInstanceNameList = get-content D:\DBA\SQLServersList.txt

#Either create manually .CSV or any other files or use New-Item cmdlet to serve this for you
$csvFilePath = "D:\DBA\SQLServerAccountList.csv"
#Write your query in double code


$query= "SELECT @@servername as SQLServerName,DSS.servicename,
DSS.startup_type_desc,
DSS.status_desc,
DSS.last_startup_time,
DSS.service_account,
getdate () as ScriptExecutedDate
FROM sys.dm_server_services AS DSS"

#Use foreach cmdlet to Test SQL connection. On successful connection, perform desired action Else write down the servers name in TXT file which were unable to setup connection 

foreach($i in $SQLInstanceNameList)



{

try
{
    Write-host "We are in Try Block Now! Good Going.........."
    
    # SQL Authentication
    #$connString = "Data Source=$$i;Database=master;User ID=YourUser;Password=YourPassword"
    
    #  "Integrated Security=True" for system logins-Windows Authentication.
    $connString = "Data Source=$i;Database=master;Integrated Security=True"

    #Create a SQL connection object
    $conn = New-Object System.Data.SqlClient.SqlConnection $connString

    #Attempt to open the connection
    $conn.Open()
    if($conn.State -eq "Open")
    {
        # We have a successful SQL connection here...
        # Notify of successful connection
        Write-Host "SQL Test connection successful"
        #Read-host "Do you want to Proceed???.......Press any Keys on Keyboard!!!!!!!"
        $QueryResult=Invoke-Sqlcmd -Query $query -ServerInstance $i | export-csv $csvFilePath -NoTypeInformation
    
       $conn.Close()
       }
       }
    
 
 catch
 {
 
Write-Host 'We could not connect to SQL server here'
Add-Content D:\DBA\CouldNotRunServersList.txt $i


  }  
    }