TSQL2sDay – Get-PostRoundup

First an apology, this round up is late!

The reason for that is an error in the PowerShell testing module Pester (That’s not completely true as you shall see!!)

I spoke in Stuttgart at the PowerShell Saturday last weekend and had intended to write this blog post whilst travelling, unfortunately I found a major error in Pester (again not strictly true but it makes a good story!!)

I explained it with this slide in my presentation

Yep, I forgot to pack my NUC with my VMs on it and had to re-write all my demos!!

But anyway, on to the TSQL2sDay posts

What a response. You wonderful people. I salute you with a Rimmer salute

There are 34 TSQL2sDay posts about dbatools, about starting with PowerShell, If you should learn PowerShell, SSAS, SSRS, Log Shipping, backups, restores, Pester, Default settings, best practices, migrations, Warnings in Agent Jobs, sqlpackage, VLFs, CMS, Disabling Named Pipes, Orphaned users, AG Status, AG Agent Jobs, logging, classes, auditing, copying files, ETL and more.

I am really pleased to see so many first timers to the TSQL2sDay blog monthly blog party. Please don’t let this be your only TSQL2sDay post. Come back next month and write a post on that topic.

Here they are below in the media of tweets, so that you can also go and follow these wonderful people who are so willing to share their knowledge. Say thank you to them, ask them questions, interact.

Learn, Share, Network

Volker wrote about testing best practices with dbatools

Dave explains why PowerShell is so useful to him in his ETL processes

Steve writes about the time he has saved using PowerShell to automate restores and audit SQL Server instances

Nate talks about copying large files like SQL Server backups using BITS with PowerShell

Warren talks about his experience as a beginner, the amount of things he automates and his DBReboot module

THANK YOU every single one and apologies if I have missed anyone!

 

 

Quickly Creating Test Users in SQL Server with PowerShell using the sqlserver module and dbatools

One of the most visited posts on my blog is nearly two and half years old now – Add User to SQL Server Database Role with PowerShell and Quickly Creating Test Users. I thought it was time to update it and use the latest sqlserver module and the dbatools module.

You can get the latest version of the sqlserver module by installing SSMS 2016. The PASS PowerShell Virtual Chapter have created a short link to make this easier for you to remember: https://sqlps.io/dl

Once you have downloaded and installed SSMS you can load the module.

Import-Module sqlserver

There is one situation where you will get an error loading the sqlserver module into PowerShell. If you have the SQLPS module already imported then you will get the following error:

Import-Module : The following error occurred while loading the extended type data file:

sqlserver-module-error

In that case you will need to remove the SQLPS module first.

Remove-Module sqlps
Import-Module sqlserver

The original post dealt with creating a number of test users for a database and assigning them to different roles quickly and easily.

First let’s quickly create a list of Admin users and a list of Service Users and save them in a text file.

$i = 0
while($I -lt 100)
{
"Beard_Service_User$i" | Out-File 'C:\temp\Users.txt' -Append
$i++
}

$i = 0
while($I -lt 10)
{
"Beard_Service_Admin_$i" | Out-File 'C:\temp\Admins.txt' -Append
$i++
}

Now that we have those users in files we can assign them to a variable by using Get-Content

$Admins = Get-Content 'C:\temp\Admins.txt'

Of course we can use any source for our users – a database, an excel file, Active Directory or even just type them in.

We can use the Add-SQLLogin command from the sqlserver module to add our users as SQL Logins, but at present we cannot add them as database users and assign them to a role.

If we want to add a Windows Group or a Windows User to our SQL Server we can do so using:

Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType WindowsUser -DefaultDatabase tempdb -Enable -GrantConnectSql 

Notice that we need to enable and grant connect SQL to the user.

If we want to add a SQL login the code is pretty much the same but we either have to enter the password in an authentication box or pass in a PSCredential object holding the username and password. Keeping credentials secure in PowerShell scripts is outside the scope of this post and the requirement is for none-live environments so we will pass in the same password for all users as a string to the script. You may want or be required to achieve this in a different fashion.

 $Pass = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Pass
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential

We can ensure that we are not trying to add logins that already exist using

 if(!($srv.Logins.Contains($User)))
{

The $srv is a SQL Server Management Server Object which you can create using a snippet. I blogged about snippets here and you can find my list of snippets on github here. However, today I am going to use the dbatools module to create a SMO Server Object using the Connect-DbaSqlServer command and assign the server and the database to a variable:

 # Create a SQL Server SMO Object
$srv = Connect-DbaSqlServer -SqlServer $server
$db = $srv.Databases[$Database]

Once we have our Logins we need to create our database users:

 $usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
$usr.Login = $User
$usr.Create()

and add them to a database role.

#Add User to the Role
$db.roles[$role].AddMember($User)

I created a little function to call in the script and then simply loop through our users and admins and call the function.

foreach($User in $Users)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $Userrole  -LoginType SQLLogin
}

foreach($User in $Admins)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $adminrole  -LoginType SQLLogin
}

To check that they have been added correctly I simply use the Get-DbaRoleMember command from dbatools and output it to Out-GridView using the alias ogv as I am on the command line:

Get-DbaRoleMember -SqlInstance $server |ogv

which looks like this:

 

get-dbarole-memebr

Once we need to clean up the logins and users we can use the Get-SQLLogin and Remove-SQLLogin commands from the sqlserver module to remove the logins and if we do that first we can then use the dbatools command Remove-SQLOrphanuser to remove the orphaned users 🙂 (I thought that was rather cunning!)

(Get-SqlLogin -ServerInstance $server).Where{$_.Name -like '*Beard_Service_*'}|Remove-SqlLogin

Remove-SQLOrphanUser -SqlServer $Server -databases $database

The Remove-SQLLogin will prompt for confirmation and the result of the Remove-SQLOrphanUser looks like this

remove-them-all

When you are looking at doing this type of automation with PowerShell, you should remember always to make use of Get-Command, Get-Help and Get-Member. That will enable you to work out how to do an awful lot. I have a short video on youtube about this:

 

and when you get stuck come and ask in the SQL Server Slack at https://sqlps.io/slack. You will find a powershellhelp channel in there.

Here is the complete code:

#Requires -module sqlserver
#Requires -module dbatools

### Define some variables
$server = ''
$Password = "Password"
$Database = 'TheBeardsDatabase'
$Admins = Get-Content 'C:\temp\Admins.txt'
$Users = Get-Content 'C:\temp\Users.txt'
$LoginType = 'SQLLogin'
$userrole =  'Users'
$adminrole = 'Admin'

# Create a SQL Server SMO Object
$srv = Connect-DbaSqlServer -SqlServer $server
$db = $srv.Databases[$Database]

function Add-UserToRole
{
param
(
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$Password,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$User,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$Server,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateNotNullOrEmpty()]
[string]$Role,
[Parameter(Mandatory=$true,
ValueFromPipeline=$true,
ValueFromPipelineByPropertyName=$true,
ValueFromRemainingArguments=$false)]
[ValidateSet("SQLLogin", "WindowsGroup", "WindowsUser")]
[string]$LoginType
)

if(!($srv.Logins.Contains($User)))
{
if($LoginType -eq 'SQLLogin')
{
$Pass = ConvertTo-SecureString -String $Password -AsPlainText -Force
$Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $User, $Pass
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql -LoginPSCredential $Credential
}
elseif($LoginType -eq 'WindowsGroup' -or $LoginType -eq 'WindowsUser')
{
Add-SqlLogin -ServerInstance $Server -LoginName $User -LoginType $LoginType -DefaultDatabase tempdb -Enable -GrantConnectSql
}
}
if (!($db.Users.Contains($User)))
{

# Add user to database

$usr = New-Object ('Microsoft.SqlServer.Management.Smo.User') ($db, $User)
$usr.Login = $User
$usr.Create()

}
#Add User to the Role
$db.roles[$role].AddMember($User)
}

foreach($User in $Users)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $Userrole -LoginType SQLLogin
}

foreach($User in $Admins)
{
Add-UserToRole -Password $Password -User $user -Server $server -Role $adminrole -LoginType SQLLogin
}

Get-DbaRoleMember -SqlInstance $server |ogv

Happy Automating!

 

 

 

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

Checking Drive Sizes with PowerShell

I have developed a series of PowerShell functions over time which save me time and effort whilst still enabling me to provide a good service to my customers.  I call it my PowerShell Box of Tricks and this is another post in the series.

Todays question which I often get asked is How much space is free on the drive?

A question often asked by developers during development and by DBAs when looking at provisioning new databases so I use this simple function to return the drive sizes using a WMI call with PowerShell

I first write the date out to the console with the Server name as I found that useful to show how much space had been freed when archiving data. Then a WMI query and a bit of maths and output to the console. The /1GB converts the drive size to something meaningful and you can see how the PercentFree is calculated from the two values using “{0:P2}”

image

Call it like this

image

and here are the results from my Azure VM. (See My previous posts on how to create your own Azure VMs with PowerShell)

image

You can find the script below

 

SQL login object permissions via PowerShell

As you know, I love PowerShell!

I use it all the time in my daily job as a SQL DBA and at home whilst learning as well.

Not only do I use PowerShell for automating tasks such as Daily Backup Checks, Drive Space Checks, Service Running Checks, File Space Checks, Failed Agent Job Checks, SQL Error Log Checks, DBCC Checks and more but also for those questions which come up daily and interfere with concentrating on a complex or time consuming task.

I have developed a series of functions over time which save me time and effort whilst still enabling me to provide a good service to my customers. I keep them all in a functions folder and call them whenever I need them. I also have a very simple GUI which I have set up for my colleagues to enable them to easily answer simple questions quickly and easily which I will blog about later. I call it my PowerShell Box of Tricks

I am going to write a short post about each one over the next few weeks as I write my presentation on the same subject which I will be presenting to SQL User Groups.

Todays question which I often get asked is What permissions do users have on that server?

In the last post on Checking SQL Server User Role Membership with PowerShell we checked the permissions a user had across the estate, this one answers the question about all users on a server.

This is generally asked by DBAs of each other Smile, auditors and the owners of the service

The first part of the script is very similar to the last post on Checking SQL Server User Role Membership with PowerShell but we use the EnumMembers method to display the members of the roles.

image

The second part – the object permissions comes with thanks to David Levy via This Link

image

To call it simply load the function

image

and a report

image

You can get the code here