By: Tim Smith | Comments (1) | Related: > PowerShell
Problem
In this tip we'll look at getting started using PowerShell with SQL Server stored procedures. We'll look at getting data from stored procedures to files or PowerShell objects that we can use further. We'll also look at passing in parameters to stored procedures that we may use in write operations. And we'll look at security around executing stored procedures with PowerShell. From built-in expressions for ease of use to custom functions, we'll see how and where we can apply these tools for our needs.
Solution
For our examples, we will create 1 data set and 3 stored procedures. We'll use the data set for both reporting information, as well as adding data with the stored procedures.
The business rules of our data limit duplicate IDs and capture values that follow a V with a dash and one digit. The first procedure will be a report from our data set that we'll use to write data in other forms. The second procedure will add new data that we pass in and update existing data if the parameter for the id is already found. Our final procedure will provide lower level developers with information they need (id range) without other information while logging their checks.
CREATE DATABASE GenExAll
GO
USE GenExAll
GO
CREATE TABLE tbProcExample(
Id SMALLINT,
IdVar VARCHAR(4)
)
GO
CREATE TABLE tbLogging_tbProcExample(
LogDate DATETIME
)
GO
INSERT INTO tbProcExample
VALUES (1,'V-1')
, (2,'V-2')
, (3,'V-6')
, (4,'V-8')
, (5,'V-90')
GO
---Report
CREATE PROCEDURE stpGetData
WITH ENCRYPTION
AS
BEGIN
SELECT Id, IdVar FROM tbProcExample
END
GO
---Change or add
CREATE PROCEDURE stpChangeAdd @id SMALLINT, @data VARCHAR(4)
WITH ENCRYPTION
AS
BEGIN
DECLARE @max SMALLINT
IF EXISTS (SELECT * FROM tbProcExample WHERE Id = @id)
BEGIN
UPDATE tbProcExample SET IdVar = @data WHERE Id = @id
END
ELSE
BEGIN
INSERT INTO tbProcExample VALUES (@id,@data)
END
END
GO
---Permissions
CREATE PROCEDURE stpMaxIdCheck
WITH ENCRYPTION
AS
BEGIN
SELECT MAX(Id) MaxId, 1 AS MinId FROM tbProcExample
INSERT INTO tbLogging_tbProcExample VALUES (GETDATE())
END
GO
USE [master]
GO
CREATE LOGIN [DeveloperOne] WITH PASSWORD = 'Lookyall,thisisnotagoodpassword,sodontuseit.Mmkay?'
GO
USE [GenExAll]
GO
CREATE USER [DeveloperOne] FROM LOGIN [DeveloperOne]
GRANT EXECUTE ON stpMaxIdCheck TO [DeveloperOne]
Reading SQL Server Stored Procedure Output with PowerShell
We can create a PowerShell function to execute a stored procedure and read the output, or we use Invoke-SqlCmd for the same purpose. In this tip, we'll do both to show alternatives.
For reading in our first example, we'll use Invoke-SqlCmd and we'll apply this to returning a file with our data (in the first image below this) and by saving to a PowerShell object that we iterate through (second image below this). We can use PowerShell to generate delimited reports or delimited files using stored procedures by specifying the stored procedure in the query parameter (-Query). In the latter case of using a PowerShell object, this may be much more useful for some data validation where we want to do the validation on an object by object basis (or row by row in SQL Server) and we want to run this through PowerShell instead of a T-SQL loop.
Invoke-Sqlcmd -ServerInstance "OurServer" -Database "OurDatabase" -Query "EXEC stpGetData" | Export-Csv "C:\files\procs\tbProcExample.csv" -NoTypeInformation
$rrex = Invoke-Sqlcmd -ServerInstance "OurServer" -Database "OurDatabase" -Query "EXEC stpGetData"
foreach ($rrec in $rrex)
{
Write-Host ("For the ID of " + $rrec.Id + " the value is " + $rrec.IdVar)
}
Here is the output written to a text file.
Here is the output written to the console.
Let's take this latter step further by identifying what "V" values don't align with the expression of one digit between one and eight following the alphabetic v character with a dash ("V-[1-8]{1}").
We can see from the above data that PowerShell will output the value (V-90) that does not match this expression and this may be a useful validation tool in some situations where we need to look at data on a granular level by rows of data, instead of sets of rows of data.
Here is the PowerShell code:
$rrex = Invoke-Sqlcmd -ServerInstance "OurServer" -Database "OurDatabase" -Query "EXEC stpGetData"
foreach ($rrec in $rrex)
{
if ($rrec.IdVar -notmatch "V-[1-8]{1}")
{
$rrec.Id
}
}
Here is the output. We can see ID 5 does not conform to the values that we expect.
Calling SQL Server Parameterized Operations with PowerShell
In our next example, we'll create a custom function that calls a stored procedure with two parameters we pass to the procedure.
In the first example, we'll actually not qualify the string data so that we can see an example of not considering input, even with a stored procedure. In a similar manner to the Invoke-SqlCmd option, we're running a non-query (in this case, a procedure) that uses the values of the parameters we pass into it.
We will run this for Id = 5 with a IdVar value of V-8 and this should update the data for this row to V-8 from V-90.
Function Execute-Procedure {
Param(
[Parameter(Mandatory=$true)][int]$id
, [Parameter(Mandatory=$true)][string]$idvar
)
Process
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=OurServer;Initial Catalog=OurDatabase;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$cmd.CommandText = "EXEC stpChangeAdd $id,'$idvar'"
try
{
$scon.Open()
$cmd.ExecuteNonQuery() | Out-Null
}
catch [Exception]
{
Write-Warning $_.Exception.Message
}
finally
{
$scon.Dispose()
$cmd.Dispose()
}
}
}
Execute-Procedure -id 5 -idvar "V-8"
After running the above, we can query the table using SQL Server Managment Studio (SSMS) and see the row where Id = 5 has been updated.
Adding a Business Rule to the PowerShell Script
We've intentionally written this PowerShell script incorrectly, as our example business rules in this case for our IdVar data follow the format of V with a dash (-) and a number between 1 and 8. There are many ways to check input in both T-SQL and using .NET, however, we can use some basics in PowerShell to do this as well.
Rather than accept string data, we'll change our parameter to only accept an integer between the values of 1 and 8 (the new parameter being $idvarno). We'll then add this integer to a string that we'll use in a parameter that is passed to the stored procedure. We'll call this stored procedure three times, the first two times with invalid values (0 and 9), and the last time with a valid value of 3.
Function Execute-Procedure {
Param(
[Parameter(Mandatory=$true)][int]$id
, [Parameter(Mandatory=$true)][ValidateRange(1,8)][int]$idvarno
)
Process
{
$scon = New-Object System.Data.SqlClient.SqlConnection
$scon.ConnectionString = "Data Source=OurServer;Initial Catalog=OurDatabase;Integrated Security=true"
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.Connection = $scon
$cmd.CommandTimeout = 0
$idvar = ("V-" + $idvarno.ToString())
$cmd.CommandText = "EXEC stpChangeAdd $id,'$idvar'"
try
{
$scon.Open()
$cmd.ExecuteNonQuery() | Out-Null
}
catch [Exception]
{
Write-Warning $_.Exception.Message
}
finally
{
$scon.Dispose()
$cmd.Dispose()
}
}
}
Execute-Procedure -id 5 -idvar "0"
Execute-Procedure -id 5 -idvar "9"
Execute-Procedure -id 5 -idvar "3"
Here is the ouptut after running the above PowerShell script.
We see that the 3 value passes and the 5th Id in our table becomes "V-3" while the other two values that lie outside the range of our PowerShell validation fail.
At the basic level, this is one way we can validate input with PowerShell. We can take this even further and be strict with our stored procedure as well, but for the sake of this example, we see that at minimum we want to be strict with input on the parameter level. If we can get away from using strings as input, we should and we should always be strict with string limits when we have to use them.
Running Stored Procedures with PowerShell with Limited Permissions
In some contexts, limited access matters more than convenience of development. Stored procedures give us the ability to provide execute access to users without direct object access - and this includes logging activity without the user knowing activity is being logged. This can be incredibly useful with a tool like PowerShell, which can use the .NET library - a powerful library that may be unfortunately misused by the wrong individual with too much access.
In development contexts where people may use PowerShell or SSMS for reviewing data or even writing to tables, we can create procedures for this access. Since this does add costs, we should consider when this is an appropriate step to take. In the below PowerShell code (we can also run derivatives of these statements in SSMS), we execute PowerShell as a lower level developer user and can see the information we get and we can't get.
$server = "OurServer" $database = "OurDatabase" $user = "DeveloperOne" $password = "Lookyall,thisisnotagoodpassword,sodontuseit.Mmkay?" ### We 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
Look what happens when we try to look directly at the tables involved in the procedure - we see errors.
It's worth noting that our users would get the same errors if they tried running the last two queries as well in SSMS. For this reason, we may give our users some access to tables through procedures whether for queries or for adding data while the users have no access to the underlying objects.
As we see with PowerShell functions, such as the built in Invoke-SqlCmd function, we can quickly create files from stored procedures, which can be useful for data migration or reporting. We can also create our own functions (or use built-in functions) to execute write operations with procedures, which may offer more security in some contexts. Finally, we may to consider restrictions for the user and account running the PowerShell function and we looked at some security basics for these operations.
Next Steps
- In contexts where PowerShell may be useful for data migration (speed and security), we see that we can create delimited files for migrating data or for reports with one line.
- Because we can give less permissions with procedures and we can use these with PowerShell, these may offer a better alternative for both reading and writing when using PowerShell than direct table or view access.
About the author
Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
