Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Verify Database Access with PowerShell

DZone's Guide to

Verify Database Access with PowerShell

Even when there is no SQL Client tooling installed on a machine, PowerShell can be used to execute SQL. Here's how to use this capability to verify you have DBO rights on a server only reachable via a webserver with no SQL tooling.

· Database Zone ·
Free Resource

Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Even when there is no SQL Client tooling installed on a machine, PowerShell can be used to execute SQL. I recently had to verify that I had been granted DBO rights on a database that was on a server only reachable from a web server. The web server of course had no SQL tooling whatsoever installed. To do that I created a small Powershell script that only relies on the .NET Framework.

The typical recommendation for executing SQL commands from Powershell is to use the Invoke-Sqlcmd cmdlet. I’m sure its a good tool, but unfortunately it isn’t installed by default. Sometimes installing extra software is not an option, so instead I’ve used PowerShell’s built in capability to create and use .NET objects. The SQL client objects are included in the default .NET framework installation, so this should work on any Windows machine.

The script accepts a connection string and tries to connect to that database, read the schemas and finally create a table. The last step is done within a transaction that is rolled back, to ensure that no change is done to the database.


param (
[Parameter(Mandatory=$true)]
[string]$connectionString
)

$ErrorActionPreference = "Stop"

Write-Host -NoNewline -ForegroundColor Gray "Trying to open connection to the database... " 

$connection = New-Object -TypeName System.Data.SqlClient.SqlConnection -ArgumentList $connectionString

$connection.Open();

Write-Host -ForegroundColor Green "Success!"
Write-Host -NoNewline -ForegroundColor Gray "Trying to query list of schemas... "

$command = $connection.CreateCommand();
$command.CommandText="IF EXISTS (SELECT 1 FROM sys.schemas) SELECT 1 ELSE SELECT 0"
$result = $command.ExecuteScalar()

if($result -eq "0")
{
throw "Can't read from database."
}

Write-Host -ForegroundColor Green "Success!"
Write-Host -NoNewline -ForegroundColor Gray "Trying to start a database transction... "

$transaction = $connection.BeginTransaction();
$command.Transaction = $transaction;

Write-Host -ForegroundColor Green "Success!"
Write-Host -NoNewline -ForegroundColor Gray "Trying to create a table... "

$command.CommandText="CREATE TABLE Dummy(id int identity(1,1))"
$result = $command.ExecuteNonQuery();

Write-Host -ForegroundColor Green "Success!"
Write-Host -NoNewline -ForegroundColor Gray "Rolling back transaction (effectively removing the table)... "

$transaction.Rollback();

Write-Host -ForegroundColor Green "Success!"
Write-Host
Write-Host "You have sufficient rights to create a table in the target database. This"
Write-Host "typically means that you are a member of the dbo role."


The output should be self-explanatory


C:\Users\andabe\Desktop> .\CheckDboPermission.ps1 "Data Source=.;Initial Catalog=Empty;Integrated Security=True"
Trying to open connection to the database... Success!
Trying to query list of schemas... Success!
Trying to start a database transction... Success!
Trying to create a table... Success!
Rolling back transaction (effectively removing the table)... Success!

You have sufficient rights to create a table in the target database. This
typically means that you are a member of the dbo role.


Compliant Database DevOps and the role of DevSecOps DevOps is becoming the new normal in application development, and DevSecOps is now entering the picture. By balancing the desire to release code faster with the need for the same code to be secure, it addresses increasing demands for data privacy. But what about the database? How can databases be included in both DevOps and DevSecOps? What additional measures should be considered to achieve truly compliant database DevOps? This whitepaper provides a valuable insight. Get the whitepaper

Topics:
powershell ,sql ,windows ,.net

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}