The PowerShell Box Of Tricks GUI

When I started as a DBA at MyWork I faced a challenge. Many hundreds of databases, dozens of servers and no idea what was on where. It was remembering this situation when new team members were appointed that lead me to write the Find-Database script and I had written a simple GUI using Read-Host to enable the newbies to see the functions I had created

image

Whilst writing this series of posts I decided that I would create a new GUI

image

I wanted the choice to be made and then the form to close so I had to use a separate function for calling all the functions referenced in the form. This function takes an input $x and depending on the value runs a particular code block. Inside the code block I ask some questions using Read-Host to set the variables, load the function and run it as shown below for Show-DriveSizes

image

Then I set about creating the GUI. First we load the Forms Assembly, create a new Form object and add a title

image

Then using the details found here I I converted the image to ASCI and use it as the background image and set the size of the Form

image

I choose a default font for the form. Note there are many many properties that you can set for all of these objects so use your best learning aid and find the ones you need.

image

I then create three labels. I will show one. I think the code is self-explanatory and you will be able to see what is going on. Don’t forget to the last line though! That adds it to the form, if you miss it you can spend a few minutes scratching your head wondering why it hasn’t appeared!!!

image

We need a Text Box for the User to put their choice in. Again the code is fairly easy to understand

image

The next bit of code enables the user to use Enter and Escape keys to Go or to Quit. Notice that both call the Close() method to close the Form and return to the PowerShell console

image

Add a button for OK and one for quit

image

and finally Activate the Form, Show it and run the function to call the correct function

image

The Return-Answer function simply calls the Return-Function function. I am not sure if that is the best way of doing it but it works in the way i wanted it to

image

Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users

There is a newer up to date version of this post here using the dbatools module and the sqlserver module

 

But if you want to continue with this way read on!!

Having created Windows Users or SQL Users using the last two days posts, today we shall add them to a role on a database.

As I discussed previously I believe that to follow good practice I try to ensure that database permissions are granted by role membership and each role is created with the minimum amount of permissions required for successful execution of the task involved.

So with each database having the correct roles created and the users created we just need to add the user to the database and to the role. This is easily done with PowerShell.

image

The Add-UserToRole function takes four parameters Server,Database,User and Role and does a series of error checks.

With these functions you can easily create a number of Users and add them to database roles quickly and easily and repeatedly.

If the test team come to you and require 10 Test Users and 3 Test Administrators adding to the test database. I create 2 notepad files

image  image

and use them with the Add-SQLAccountToSQLRole and Add-UserToRole functions to create the users

image

Here are the results in PowerShell

image

and in SSMS

image

The Code is here

 

Creating a Windows User and adding to a SQL Server Role with PowerShell

Another post in the PowerShell Box of Tricks series.

In a previous post Checking SQL Server User Role Membership we showed how to check which roles users were added to. This function allows you to add Windows Users to Server Roles. A nice simple function which can easily be piped into to allow users to be added form a list in a text file, csv file or even from Active Directory. This makes it easy to recreate Dev and Test environments and can be added to Disaster Recovery processes.

We create a Login Object, set the Logintype and create it with the Create Method. It is then added to the Role specified.

image

The function does some simple error checking. If the login already exists on the server it will just add it to the role and if the role has been mistyped it will let you know. It does this by checking if the Role object is Null for the Roles and the Contains Method for the Logins

image

The function is called as follows. To just create a login I add the user to the public role

Add-WindowsAccountToSQLRole FADE2BLACK ‘FADE2BLACK\Test’ public

The code can be found here

 

Tomorrow we will create and add a SQL Authenticated User

Displaying the Windows Event Log with PowerShell

The latest post in the  PowerShell Box of tricks series is here.

I’ll start by saying this is a bit of a cheat. PowerShell has a perfectly good cmdlet called Get-EventLog and plenty of ways to use it

I created this function because I like to use Out-GridView and specify only a few records so it is quicker on remote systems. I like Out-GridView as it enables me to filter easily by typing in the top box. This is most often used via the simple GUI I have created. At the command line I would just use Get-EventLog

image

The function is shown below. It is important to know that the $log parameter is CaSeSensItive

image

The Code is here

 

Showing and Killing SQL Server Processes with PowerShell

Another post in the PowerShell Box of Tricks series. There are much better ways of doing this I admit but as you can do it with PowerShell I created a function to do it.

Create a Server Object and notice that there is a Method named EnumProcesses by piping it to Get-Member and then look at the Properties and Methods of EnumProcesses

image

Once I had done that then it was easy to create a function to display what is going on. It’s quick and easy. Not as good as sp_WhoIsActive but it displays about the same info as sp_who, sp_who2

image

image

You can also find a Method called KillProcess on the Server Property so I asked a Yes/No question using Windows Forms. You can find much more detail on that here

image

All you need to supply is the spid

The code is here Show-SQLProcesses

Show The Last Backups On A Server with PowerShell

Another day another PowerShell Box of Tricks post

Auditors, managers and bosses often want proof of following processes successfully so when they come knocking on my door(I don’t have a door, it’s usually my shoulder they knock) asking when the last backups were taken I either use this function or show them the excel file my automated process creates. This depends on if I think the pretty colours in the excel sheet will impress them!

The Show-LastServerBackup function iterates through each database on the server and takes each of the three properties mentioned in yesterdays post. However this time I created an empty hash table and added each result to it as follows

I created the hash table with @() and then assign each property to a variable inside the loop and add it to a temporary PSObject with some custom NoteProperties to fit the data

image

The last line adds the temporary object to the hash table. I then simply pipe the hash table to Format-Table to show the results

Call it like this

image

Ooops I haven’t backed up my system databases! Luckily it is my Azure server for blogging and presenting and is torn down and recreated whenever it is needed

You can get the code here