PowerShell is a powerful scripting language that can be used to automate tasks and perform various operations on SQL Server. In this article, we will explore how to execute SQL Server stored procedures using PowerShell.

Getting Data from Stored Procedures

One of the common tasks in SQL Server is retrieving data from stored procedures. PowerShell provides two methods to achieve this: using the Invoke-SqlCmd cmdlet or creating a custom function.

Here is an example of using the Invoke-SqlCmd cmdlet to execute a stored procedure and export the result to a CSV file:

Invoke-SqlCmd -ServerInstance "OurServer" -Database "OurDatabase" -Query "EXEC stpGetData" | Export-Csv "C:\files\procs\tbProcExample.csv" -NoTypeInformation

Alternatively, you can save the result to a PowerShell object and iterate through it:

$result = Invoke-SqlCmd -ServerInstance "OurServer" -Database "OurDatabase" -Query "EXEC stpGetData"

foreach ($row in $result)
{
    Write-Host ("For the ID of " + $row.Id + " the value is " + $row.IdVar)
}

Executing Parameterized Stored Procedures

PowerShell allows you to execute parameterized stored procedures by creating a custom function. Here is an example:

Function Execute-Procedure {
    Param(
        [Parameter(Mandatory=$true)][int]$id,
        [Parameter(Mandatory=$true)][string]$data
    )
    Process
    {
        $connectionString = "Data Source=OurServer;Initial Catalog=OurDatabase;Integrated Security=true"
        $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
        
        $command = New-Object System.Data.SqlClient.SqlCommand
        $command.Connection = $connection
        $command.CommandText = "EXEC stpChangeAdd $id, '$data'"

        try
        {
            $connection.Open()
            $command.ExecuteNonQuery() | Out-Null
        }
        catch [Exception]
        {
            Write-Warning $_.Exception.Message
        }
        finally
        {
            $connection.Dispose()
            $command.Dispose()
        }
    }
}

Execute-Procedure -id 5 -data "V-8"

Security Considerations

When executing stored procedures with PowerShell, it is important to consider security. You can limit access by creating procedures that provide execute access without direct object access. This allows you to log activity without the user knowing it is being logged.

Here is an example of executing PowerShell with limited permissions:

$server = "OurServer"
$database = "OurDatabase"
$user = "DeveloperOne"
$password = "Lookyall,thisisnotagoodpassword,sodontuseit.Mmkay?"

# Get the max and min Ids
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query "EXEC stpMaxIdCheck" -Username $user -Password $password

# Fails - permissions denied
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query "SELECT * FROM tbProcExample" -Username $user -Password $password

# Fails - permissions denied
Invoke-Sqlcmd -ServerInstance $server -Database $database -Query "SELECT * FROM tbLogging_tbProcExample" -Username $user -Password $password

By executing PowerShell with limited permissions, you can control access to tables and ensure that users only have access to the necessary procedures.

Conclusion

PowerShell provides a convenient and powerful way to execute SQL Server stored procedures. Whether you need to retrieve data, execute parameterized operations, or enforce security measures, PowerShell can help you automate and streamline your SQL Server tasks.