Use PowerShell to Identify the Process ID for SQL Server Services

I recently saw a blog article on How to Identify Process ID for SQL Server Services? – Interview Question of the Week #185 written by Pinal Dave. While his answer is simple with TSQL, what if you’re not a SQL guy? You can also retrieve this information with PowerShell from Windows itself. When it comes to retrieving information about Windows services with PowerShell, the first command that comes to mind is Get-Service....

April 25, 2019 · 2 min · 261 words · Mike F. Robbins

Run SQL Server PowerShell Cmdlets as a Different User

One of the ways I practice the principal of least privilege is by logging into my computer as a domain user who is also a standard local user. I typically run PowerShell as a domain user who is a local admin and elevate on a per command basis using a domain account with more access only when absolutely necessary. The problem I’ve run into is neither the account I’m logged into my computer as or the one I’m running PowerShell as has the ability to execute SQL queries that I need to run against various SQL servers in my environment....

April 11, 2019 · 3 min · 507 words · Mike F. Robbins

Resolving Microsoft SQL Server Error 4064 with PowerShell

Recently, a fellow IT Pro contacted me and stated they were unable to login to one of their SQL Servers using Windows Authentication. The following error was generated when attempting to login to SQL Server Management Studio (SSMS). Their exact words were “I think we have a permissions problem”. Clicking on the “Show technical details” icon at the bottom of that error message showed the following information. You can work around this problem by clicking on the “Options »” button:...

January 11, 2019 · 3 min · 614 words · Mike F. Robbins

PowerShell One-Liner to Disable Active Directory Accounts and Log the Results to a SQL Server Database

The new PowerShell cmdlets that are part of the SQLServer PowerShell module that’s distributed as part of SSMS (SQL Server Management Studio) 2016 make it super easy to write the output of PowerShell commands to a SQL Server database. The ActiveDirectory PowerShell module that’s part of the RSAT (Remote Server Administration Tools) is also required by the code shown in this blog article. This PowerShell one-liner retrieves a list of Active Directory users who have not logged in within the past 120 days, are enabled, and exist in the Adventure Works OU (Organizational Unit)....

February 16, 2017 · 2 min · 322 words · Mike F. Robbins

Write a GUI on Top of Existing PowerShell Functions with SAPIEN PowerShell Studio 2016

This blog article will demonstrate how to write a GUI on top of your existing PowerShell functions using SAPIEN PowerShell Studio 2016. I’ve previously written a couple of functions for managing SQL Server agent jobs. These two functions, Get-MrSqlAgentJobStatus and Start-MrSqlAgentJob can be found in my SQL repository on GitHub. Launch PowerShell Studio. Select file, the arrow next to new, and new form: For this particular GUI, I’ll select the “Dialog Style Template” since I want a fixed border without any minimize or maximize buttons:...

November 24, 2016 · 3 min · 521 words · Mike F. Robbins

PowerShell Function to Check the Status of a SQL Agent Job using the .NET Framework

My previous blog article demonstrated how to start a SQL agent job using the .NET Framework from PowerShell to eliminate the dependency of needing the SQL Server PowerShell module or snap-in on the machine where the command is being run from. There’s not much use of blindly starting a SQL agent job without being able to check the status of it so I decided to write another function to accomplish that task....

November 23, 2016 · 3 min · 510 words · Mike F. Robbins

Start a SQL Agent Job with the .NET Framework from PowerShell

As of this writing, the most recent version of the SQLServer PowerShell module (which installs as part of SQL Server Management Studio) includes cmdlets for retrieving information about SQL agent jobs, but no cmdlets for starting them. Get-Command -Module SQLServer -Name *job* I recently ran into a situation where I needed to start a SQL agent job from PowerShell. The solution needed to be a tool that others could use who may or may not have the SQLServer module, SQLPS module or older SQL Server snap-in installed....

November 22, 2016 · 3 min · 539 words · Mike F. Robbins

Video: Automate Operational Readiness and Validation Testing of SQL Server with PowerShell and Pester

I recently presented a session on “Automate Operational Readiness and Validation Testing of SQL Server with PowerShell and Pester” for the PowerShell Virtual Chapter of SQL PASS. The video from that presentation is now available: Both the code and slide deck from the presentation can be found in my presentations repository on GitHub. µ

November 17, 2016 · 1 min · 54 words · Mike F. Robbins

Store and Retrieve PowerShell Hash Tables in a SQL Server Database with Write-SqlTableData and Read-SqlTableData

In my blog article from last week, I demonstrated using several older open source PowerShell functions to store the environmental portion of the code from operational validation tests in a SQL Server database and then later retrieve it and re-hydrate it back into a PowerShell hash table. Earlier this week, a new release of the SQLServer PowerShell module was released as part of SSMS (SQL Server Management Studio): It includes three new cmdlets, two of which can be used to store and retrieve data in a SQL Server database from PowerShell instead of the older open source ones that I demonstrated in the previously referenced blog article from last week....

September 22, 2016 · 3 min · 462 words · Mike F. Robbins

Video: Building Unconventional SQL Server Tools in PowerShell

Last week, on Wednesday (April 6th, 2016), I presented a session at the PowerShell and DevOps Global Summit 2016 on “Building Unconventional SQL Server Tools in PowerShell with Functions and Script Modules”. The video from that presentation is now available: Here’s the abstract or synopsis for this presentation: “Have you ever had records from a SQL Server database table come up missing? Maybe someone or some process deleted them, but who really knows what happened to them?...

April 15, 2016 · 2 min · 271 words · Mike F. Robbins

Converting a SQL Server Log Sequence Number with PowerShell

As demonstrated in one of my previous blog articles Determine who deleted SQL Server database records by querying the transaction log with PowerShell, someone or something has deleted records from a SQL Server database. You’ve used my Find-MrSqlDatabaseChange function to determine when the delete operation occurred based on information contained in either transaction log backups or the active transaction log: Find-MrSqlDatabaseChange -ServerInstance SQL01 -Database pubs -StartTime (Get-Date -Date '03/28/2016 14:55 PM') You’re ready to perform point in time recovery of the database to the LSN (Log Sequence Number) just before the delete occurred but the LSN provided from the transaction log is in a different format than what’s required to perform a restore of the database....

March 31, 2016 · 4 min · 642 words · Mike F. Robbins

Determine who deleted SQL Server database records by querying the transaction log with PowerShell

Have you ever had a scenario where records from one or more SQL server database tables mysteriously came up missing and no one owned up to deleting them? Maybe it was an honest mistake or maybe a scheduled job deleted them. How do you figure out what happened without spending thousands of dollars on a third party product? You need to determine what happened so this doesn’t occur again, but the immediate crisis is to get those records back from a restored copy of the database....

July 16, 2015 · 6 min · 1266 words · Mike F. Robbins