Grant full permission to Windows folders and files

For a Windows application or service, it is often necessary for either the user or service startup account to have full control to its folders and files. In most cases, this is transparent to the user. It is generally being taken care of by various delegation mechanisms behind the scene. However, this can be a problem during application upgrade or migration, when one rearranges disk drive letters or mount point names, or moves things from one server to another. To resolve this, one could manually go to each file and folder property page through Windows Explorer, then the Security tab, then the Edit… button, then the Add… button, etc. This method isn’t too bad if only a handful of files and folders are involved. But it is easy to see that manual method gets old really quickly, is error prone, and does not scale.

For example, say you are doing SQL Server migration for a data warehouse instance, where there are multiple mount points and hundred of database files due to filegroup usage and table partitions. During the migration process, you’ll move them to a new location and attach those database files. To ensure SQL Server service SID has proper permission is no small task and we need an automated way.

Given this is Windows, I started searching around for a PowerShell solution. You’d think this is a common problem, and therefore it shouldn’t be too difficult to find a solution. But that’s not the case so I wrote my own. Have fun with it!

Notes:

  • Make sure you start PowerShell with “Start as Administrator”. This is to get around the issue that the creator and permission granter are different identities;
  • The first thing the script does is to give ownership to the Administrators group. During execution, you’ll see output like:
    SUCCESS: The file (or folder): “x:\MountPoints\A1” now owned by the administrators group.
    that is to be expected.
  • The script’s execution is recursive, meaning all sub-folders and files are covered.

Without further ado, here is the PowerShell function:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

As an example, to grant proper permission to 3 folders listd in the code, you can run the following script as administrator:

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

$folders = @("x:\MountPoints\A1", "x:\MountPoints\A2\", "x:\MountPoints\B1")

$folders | foreach {
    grantFullPermission $_ 'NT SERVICE\MSSQLSERVER' }

You could also make this a short program that takes two parameters: a folder and an account. Remember to quote the parameters if they have space character.

param(
[parameter(Mandatory=$true)]$DirectoryPath,
[parameter(Mandatory=$true)]$AccountName)

function grantFullPermission ($folderpath, $userAccount) {
    Invoke-Expression "takeown /f $folderpath /r /a /d Y"
    $aclWork = (Get-Item $folderPath).GetAccessControl("Access")
    $ssaf = New-Object system.Security.AccessControl.FileSystemAccessRule($userAccount, "FullControl", "ContainerInherit, ObjectInherit", "None", "Allow")
    $aclWork.SetAccessrule($ssaf)
    Set-ACL $folderPath $aclWork }

grantFullPermission $DirectoryPath $AccountName

JiMetrics now gathers SQL Server startup account

During the last few days, I’ve refactored JiMetrics and added a new function:

  • Used Pester to create more test cases for PowerShell functions I wrote;
  • Enhanced the design and code so JiMetrics also gathers SQL Server instance’s startup account, which can be useful.

If you don’t know what JiMetrics is, go to this page to find out. It’s is a tool that uses SQL Server to gather important SQL Server metrics in your enterprise. No additional software install, no registry change, no files to copy and move around that pollute your system, it only uses SQL Server, which you already have and it just works! It has been pretty useful to me and my co-workers. JiMetrics is open source and free to use. Check it out and let me know what you think.

I will keep improving this. My next objective is to play with SQL Server 2014 column-store and see if I can convert the collection database tables to column-store.

Collecting Windows BIOS and Host Serial Number

After imporving JiMetrics yesterday so it tries to determine if the host is a VM or not, I made another improvement today: collecting host server BIOS related information and serial number.

I don’t know about you, in the past when I needed to gather a Windows server’s BIOS or serial number for troubleshooting, I typically rebooted the machine, pressed whatever function key was necessary to get into the BIOS, and coped things down. Wouldn’t it be nice if we can get it programmatically and store that somewhere for easy reference?

So today I made another enhancement to JiMetrics to address that need. Here is what I did:

  1. Added additional columns to the Windows.Host table: SMBIOSVersion, BIOSReleaseDate, SerialNumber;
  2. I made improvement to the PowerShell script, so it now uses Win32_BIOS to gather and store that in the JiMetrics database.

Together with HardwareVendor and HardwareModel, it will be easy to find the latest BIOS software for your server host. In addition, based on SMBIOSVersion and BIOSRealeaseDate, you can determine if your BIOS is out of date and decide if you want to update the host BIOS accordingly. Based on my past experience, a lot of companies have servers whose BIOS is way out of date. Armed with this information, they won’t be.

With this improvement, it is even more easier for system admins to be on top of things. JiMetrics is not just for SQL Server DBAs, a Windows admin will also find it extremely valuable. What are you waiting for? Go to jimetrics.com and get it 🙂

Determining if a Windows host is a VM in JiMetrics

I’ve been using my own SQL Server metrics collection package called JiMetrics for a couple of years. It is easy to set up. All you need are just two things: SQL Server instance and an account that has admin access to both the servers and instances you care about.

JiMetrics doesn’t do anything that will be hard to undo: no binary install, no registry changes, no cookies and temp files to store states and all that junk that pollutes your environment. Its footprint is small and just quietly collects important metrics data at an interval of your own choosing. Follow steps here and start today. As a system administrator, you will be glad to have the metrics data for analysis and decision making.

Today I made the database a bit better: I changed the database schema by adding a new column, IsVM, to the Windows.Host table. Here is the idea:

  1. IsVM tells you if a particular Windows host is a virtual machine or not;
  2. IsVM can have only one of two values: “Y” and “N”, and is not NULLable;
  3. IsVM is a persisted computed column. The computation is done via the Manufacture property of Win32_ComputerSystem class;
  4. Currently, if the Manufacure is one of “microsoft”, “xen”, or “innotek GmbH”, then IsVM value is “Y”, otherwise it is “N”.

Here is the relevant portion of the computed column definition:

	[IsVM] AS (CASE HardwareVendor 
			WHEN 'innotek GmbH' THEN 'Y'
			WHEN 'microsoft' THEN 'Y'
			WHEN 'xen' THEN 'Y'
			ELSE 'N'
		  END) PERSISTED,

Enjoy! If you have any comments on either of these questions, I’d love to hear them:
1. What other values of Manufacture in Win32_ComputerSystem indicates a VM, other than the three I listed above?
2. Are there better ways to detect if a Windows host is a VM?

Install sqlps as a PowerShell module

Most of SQL Server automation scripts using PowerShell use SMO directly. For example, one would do something like:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”)
$MyServer = new-object (‘Microsoft.SqlServer.Management.Smo.Server’) ‘HOME’
$MyDataBase = new-object (‘Microsoft.SqlServer.Management.Smo.Database’)
($Server, “MyDataBase”)

That’s a lot of typing and looks messy and cumbersome.

Since SQL Server 2008, Microsoft provides a sqlps shell which exposes a lot of SQL Server objects and functions for easy browsing and inspection, but one couldn’t import that into an existing scripts for easy automation. With the release of SQL Server 2012 Feature Pack, it is now possible to expose sqlps functions in your scripts by running:

Import-Module sqlps

Here is how to get sqlps installed so it is available to import as a module. I’ve tested this successfully on both Windows Server 2008 R2 and Windows Server 2012. To get some idea on how to use it, perhaps some of my scripts here can get you started 🙂

1. Go to SQL Server 2012 Feature Pack site

2. Download and install the 3 items below
Microsoft System CLR Types for Microsoft SQL Server 2012
Microsoft SQL Server 2012 Shared Management Objects
Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2012

BeginDigression
Given a choice, I’d rather write automation programs on Windows with either Python and Perl, because both languages come with a comprehensive testing framework that supports TDD. With test cases and various testing harness, it is much easier to build solid, high quality software solutions.

However, if for whatever reason, it is difficult to use Python or Perl, then the natural choice on Windows is PowerShell, because it comes with the OS. Too bad PowerShell does not have a solid testing framework. I have been using PSUnit, which leaves a lot to be desired for people who are used to xUnit framework, but it is still worthwhile to use.
EndDigression