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!

 

 

Export SQL User Permissions to T-SQL script using PowerShell and dbatools

There are times when DBA’s are required to export database user permissions to a file. This may be for a number of reasons. Maybe for DR purposes, for auditing, for transfer to another database or instance. Sometimes we need to create a new user with the same permissions as another user or perhaps nearly the same permissions. I was having a conversation with my good friend and MVP Cláudio Silva and we were talking about how Export-SqlUser from dbatools could help in these situations and he suggested that I blogged about it so here it is.

The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

Cláudio wrote Export-SqlUser to solve a problem. You should always start with Get-Help whenever you are starting to use a new PowerShell command

Get-Help Export-SqlUser -ShowWindow

01 - get help.PNG

The command exports users creation and its permissions to a T-SQL file or host. Export includes user, create and add to role(s), database level permissions, object level permissions and also the Create Role statements for any roles, although the script does not create IF NOT EXISTS statements which would be an improvement. It also excludes the system databases so if you are scripting users who need access to those databases then that needs to be considered. Cláudio is aware of these and is looking at improving the code to remove those limitations.

It takes the following parameters
  • SqlInstance
    The SQL Server instance name. SQL Server 2000 and above supported.
  • User
    Export only the specified database user(s). If not specified will export all users from the database(s)
  • DestinationVersion
    Which SQL version the script should be generated using. If not specified will use the current database compatibility level
  • FilePath
    The filepath to write to export the T-SQL.
  • SqlCredential
    Allows you to login to servers using alternative credentials
  • NoClobber
    Do not overwrite the file
  • Append
    Append to the file
  • Databases
    Not in the help but a dynamic parameter allowing you to specify one or many databases

Lets take a look at it in action

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Users.sql
Notepad C:\temp\SQL2016N2-Users.sql

02 - Export user server.PNG

Lets take a look at a single database

Export-SqlUser -SqlInstance SQL2016N2 -FilePath C:\temp\SQL2016N2-Fadetoblack.sql -Databases Fadetoblack
notepad C:\temp\SQL2016N2-Fadetoblack.sql

03 single database.PNG

This is so cool and so easy. It is possible to do this in T-SQL. I found this script on SQLServerCentral for example which is 262 lines and would then require some mouse action to save to a file

We can look at a single user as well. Lets see what Lars Ulrich can see on the FadeToBlack database

04 - export lars.PNG

USE [FadetoBlack]
GO
CREATE USER [UlrichLars] FOR LOGIN [UlrichLars] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT CONNECT TO [UlrichLars]
GO
DENY INSERT ON [dbo].[Finances] TO [UlrichLars]
GO
DENY SELECT ON [dbo].[RealFinances] TO [UlrichLars]
GO
GRANT SELECT ON [dbo].[Finances] TO [UlrichLars]
GO

So he can select data from the Finances table but cannot insert and cannot read the RealFinances data. Now lets suppose a new manager comes in and he wants to be able to look at the data in this database. As the manager though he wants to be able to read the RealFinances table  and insert into the Finances table. He requests that we add those permissions to the database. We can create the T-SQL for Lars user and then do a find and replace for UlrichLars with TheManager , DENY INSERT ON [dbo].[Finances] with GRANT INSERT ON [dbo].[Finances] and DENY SELECT ON [dbo].[RealFinances] with GRANT SELECT ON [dbo].[RealFinances] and save to a new file.

$LarsPermsFile = 'C:\temp\SQL2016N2-Lars-Fadetoblack.sql'
$ManagerPermsFile = 'C:\temp\SQL2016N2-Manager-Fadetoblack.sql'
Export-SqlUser -SqlInstance SQL2016N2 -FilePath $LarsPermsFile -User UlrichLars -Databases Fadetoblack
$ManagerPerms = Get-Content $LarsPermsFile
## replace permissions
$ManagerPerms = $ManagerPerms.Replace('DENY INSERT ON [dbo].[Finances]','GRANT INSERT ON [dbo].[Finances]')
$ManagerPerms = $ManagerPerms.Replace('DENY SELECT ON [dbo].[RealFinances]','GRANT SELECT ON [dbo].[RealFinances]')
$ManagerPerms = $ManagerPerms.Replace('UlrichLars','TheManager')
Set-Content -path $ManagerPermsFile -Value $ManagerPerms

I will open this in Visual Studio Code Insiders using

code-insiders $LarsPermsFile , $ManagerPermsFile

if you are not using the insiders preview remove the “-insiders”

05 - code insiders.PNG

You can right click on the Lars file and click select for compare and then right click on the Managers file and select compare with Lars File and get a nice colour coded diff

06 - compare.gif

Perfect, we can run that code and complete the request. When we impersonate Lars we get

07 - lars.PNG

but when we run as the manager we get

08 - the manager.PNG

 

Excellent! All is well.

It turns out that there is another Fadetoblack database on a SQL2000 instance which for reasons lost in time never had its data imported into the newer database. It is still used for reporting purposes. The manager needs to have the same permissions as on the SQL2016N2 instance. Obviously the T-SQL we have just created will not work as that syntax did not exist for SQL 2000 but Cláudio has thought of that too. We can use the DestinationVersion parameter to create the SQL2000 (2005,2008/20008R2,2012,2014,2016) code

We just run

Export-SqlUser -SqlInstance SQL2016N2 -Databases FadetoBlack -User TheManager  -FilePath C:\temp\S
QL2016N2-Manager-2000.sql  -DestinationVersion SQLServer2000
Notepad C:\temp\SQL2016N2-Manager-2000.sql

and our SQL2000 compatible code is created

09- manager 2000.PNG

Simply awesome. Thank you Cláudio

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

Testing SQL Server Access to a share with PowerShell using dbatools

A good security practice is to backup our SQL Servers to a network share but not allow users to be able to browse the share. How can we ensure that our SQL Server has access or test it if it has been set up by someone else?

Lets set this up.

First lets create a share for our backups

$FileShareParams=@{
Name='SQLBackups'
Description='The Place for SQL Backups'
SourceVolume=(Get-Volume-DriveLetterD)
FileServerFriendlyName='beardnuc'
}
New-FileShare @FileShareParams

This will create us a share called SQLBackups on the D drive of the server beardnuc, but without any permissions, lets grant permissions to everyone

$FileSharePermsParams=@{
 Name = 'SQLBackups'
 AccessRight = 'Modify'
 AccountName = 'Everyone'}
Grant-FileShareAccess @FileSharePermsParams

01 file share.PNG

The share is created and I can access it and create a file

02 - create a file.PNG

and as we can see the permissions are granted for everyone

03 -permissions.PNG

OK, that’s not what we want so lets revoke that permission.

Revoke-FileShareAccess Name SQLBackups AccountName 'Everyone'

04 revoked.PNG

Now lets add permissions just for our SQL Server Service Accounts

$FileSharePermsParams = @{
Name = 'SQLBackups'
AccessRight = 'Modify'
AccountName = 'SQL_DBEngine_Service_Accounts
}
Grant-FileShareAccess @FileSharePermsParams 
and explicitly deny our DBA user accounts from accessing them.
$BlockFileShareParams = @{
Name = 'SQLBackups'
AccountName = 'SQL_DBAs_The_Cool_Ones'
}
Block-FileShareAccess @BlockFileShareParams
In the GUI our permissions look like this
and when I try to access as THEBEARD\Rob I get this

07 -no permissions.PNG

So how can I check that I have access from my SQL Server? Sure I could get the password of the SQL Service account and run a process as that account, not saying that’s a good idea but it could be done. Of course it couldn’t be done if you are using Managed Service Accounts or Group Managed Service Accounts but there is a way

Enter dbatools to the rescue 😉 The dbatools module (for those that don’t know) is a PowerShell module written by amazing folks in the community designed to make administrating your SQL Server significantly easier using PowerShell. The instructions for installing it are available here It comprises of 182 separate commands at present

There is a command called Test-SqlPath As always start with Get-Help

Get-Help Test-SqlPath -Full

08 - get help.PNG

So it uses master.dbo.xp_fileexist to determine if a file or directory exists, from the perspective of the SQL Server service account, has three parameters Sqlserver, Path and SqlCredential for SQL Authentication. Of course if that stored procedure is disabled on your estate then this command will not be of use to you. With that in mind, lets run it and see what it does
Test-SqlPath -SqlServer sql2016n1 -Path \\beardnuc\SQLBackups
09 - path test

That’s good I have access, lets back a database up

Backup-SqlDatabase -ServerInstance SQL2016N1 -Database DBA-Admin -CopyOnly -BackupAction Database -BackupFile '\\BeardNuc\SQLBackups\Test-DBA-Admin.bak'
Ah, I cant show you as I don’t have access. Better get in touch with the data centre admin to check 😉 Luckily, I am my own data centre admin and have another account I can use 🙂
10 - check

So what if we want to test all of our servers for access to the new share? I tried this

$SQLServers = (Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
Test-SqlPath -SqlServer $SQLServers -Path '\\BeardNuc\SQLBackups'
but unfortunately I hit an error
11 - error.PNG
It seems that at the moment (version 0.8.942) this command only accepts a single server. This is what you should do if you find either a bug or have an idea for dbatools. Raise an issue on Github
Navigate to the GitHub repository and click on issues. I generally search for the command name in the issues to see if someone else has beaten me to it
12 - issues
If those issues don’t match yours then click the green New Issue button
There is a template to fill in which asks you to specify your Windows, PowerShell and SQL versions with the commands that you need to do so included. Please do this and paste the results in as it will help the folks to replicate the issues in the case of more complicated  bugs
I created this issue with a potential fix as well, you don’t have to do that, just letting the folks know is good enough
Until that issue is resolved, you can check all of your servers as follows
$SQLServers=(Get-VM -ComputerName beardnuc).Where{$_.Name -like '*SQL*' -and $_.Name -notlike 'SQL2008Ser2008'}.Name
foreach($Server in $SQLServers)
{
$Test = Test-SqlPath -SqlServer $Server -Path '\\BeardNuc\SQLBackups'
[PSCustomObject]@{
Server = $Server
Result = $Test
}
}
13 - servers.PNG
and if I remove one of the service accounts from the group and restart the service an run the command again
14 - one fails.PNG
So that’s how to use dbatools to check that your SQL Server have access to a Network share and also how to create an issue on GitHub for dbatools and help it to get even better

Happy Automating

NOTE – The major 1.0 release of dbatools due in the summer 2017 may have breaking changes which will stop the above code from working. There are also new commands coming which may replace this command. This blog post was written using dbatools version 0.8.942 You can check your version using

 Get-Module dbatools

and update it using an Administrator PowerShell session with

 Update-Module dbatools

You may find that you get no output from Update-Module as you have the latest version. If you have not installed the module from the PowerShell Gallery using

Install-Module dbatools

Then you can use

Update-dbatools

Listing the SQL Server SysAdmins With PowerShell

A very short blog today just to pass on this little script.

I was required to list all of the SysAdmins across a large estate. Obviously I turned to PowerShell 🙂

I iterated through my server list collection and then created a server SMO object and used the EnumServerRoleMembers method to display all of the sysadmin members

2014-04-12_152433

This will work on SQL2000 – SQL2012. You can see how you can easily change the rolename in the script to enumerate other server roles.

Another way you could do it is to use the query

 

and pass that with Invoke-SQLCMD through to every server (if you had to use Powershell 🙂 ). That query won’t work with SQL 2000 though

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