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.

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.

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:

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).

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:

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.

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.

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. µ

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.

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?

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.

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.

There are several different ways to query a SQL Server from PowerShell but most often you’ll find that they’re dependent on the SQL PowerShell module or snapin. To eliminate that dependency, you can query a SQL Server from PowerShell using the .NET framework. There are several different options to accomplish this including using a DataReader or a DataSet and there are plenty of tutorials on those topics already on the Internet so there’s no reason to duplicate that information here.

The SQL Server 2014 basic management tools have been installed on the Windows 8.1 workstation that’s being used in this blog article. When attempting to import the SQLPS (SQL Server PowerShell) module on your workstation, you’ll be unable to import it and you’ll receive the following error message if the PowerShell script execution policy is set to the default of restricted: Import-Module -Name SQLPS Import-Module : File C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\Sqlps.

I presented a beginner level session for the PowerShell Virtual Chapter of PASS yesterday titled “PS C:> Get-Started -With ‘PowerShell for SQL Server’”. The session is entry level PowerShell and designed for those people who haven’t yet embraced PowerShell because I often hear “How do I get started with PowerShell”? My new answer: Watch this video. The first two thirds or so of the presentation is more or less generic because the basics for someone who is just getting started with PowerShell are the same regardless of what product you’re using it with.

I recently set out to change the recovery model of a SQL Server database with PowerShell. There seems to be lots of information available on how to accomplish this task with PowerShell through SMO (SQL Server Management Objects) and using T-SQL wrapped inside the Invoke-Sqlcmd cmdlet. I even found lots of information about how to view the recovery model with the PowerShell SQL Server PSProvider, but when it came to actually changing the recovery model via the PSProvider, there was little if any information about how to accomplish that task.

I’m a big believer in trying to write my PowerShell code to what the industry considers to be the best practices as most are common sense anyway, although as one person once told me: “Common sense isn’t all that common anymore”. I would hope that even the most diehard best practices person would realize that if you run into a scenario where following best practices causes the results to be skewed, that at least in that scenario it’s worth taking a step back so you can see the bigger picture.

In my last blog article on Getting Started with Administering and Managing Microsoft SQL Server with PowerShell, I left off by introducing you to how to query information about your SQL Server using PowerShell with SQL Server Management Objects (SMO). In this blog article, I’ll pick up where I left off and write a reusable PowerShell function to return information about SQL Server files and file groups while making the use of SMO transparent to the user of the function.

I’ve used PowerShell to administer and manage Microsoft SQL Server for quite some time although I haven’t blogged much about it. I thought I would take the time to write a series of blog articles about using PowerShell to administer and manage Microsoft SQL Server to help others who are just getting started and to clear up some common misconceptions. Just to give you a little background information about myself, I’ve worked (professionally) in a data-center environment administering and managing every version of SQL Server since 6.

This past Saturday, I presented a session at PowerShell Saturday 003 in Atlanta. Towards the end of the presentation, I created 290 Active Directory user accounts by using the information for employees contained in the Adventure Works 2012 database. This is actually a PowerShell script that I whipped up Friday night at the hotel after the speaker dinner. I populated some demographic information by joining multiple tables together from that particular database.

I need a few Active Directory users created in my mikefrobbins.com test environment so I thought why come up with fake information when I could use information that I already have in a SQL Server database? The Employees table in the Northwind database looks like an easy enough candidate since all the data I need is in one table. This is about the concept and not about seeing how complicated I can make this process.