Building SQL Server Service Names in PowerShell Strings

In a number of the PowerShell scripts I'm writing I have to build strings that contain the service account names or Active Directory group names for security principals used by SQL Server. PowerShell variables all start with the $ character, and many of the service names SQL Server uses embed the $ character as part of the name. Fun.

One of the great features of PowerShell is that you can embed variables into a string, and if you use the double-quote character (") to delimit the string, PowerShell will perform the variable substitution, making your scripts much more versatile. For example:

$inst = "SQLTBWS\INST01"
$dbnm = "AdventureWorks"
$cnstr = "Data Source=$inst;Integrated Security=SSPI;Initial Catalog=$dbnm"

This causes the $cnstr variable to contain a string with the proper connection string to access the AdventureWorks database on the SQLTBWS\INST01 instance. I can (and usually do) easily pass arguments to my script with these values, and the script performs properly for whatever instance/database combination I require.

Because the SQL Server service names often use an embedded $ in the name I frequently have to use the "other" delimeter for strings, the single-quote ('). Using this causes PowerShell to NOT perform variable substitution, allowing me to create variables for service names, like this:

$svcnm = 'MSSQL$INST01'

All well and good. Now, in writing my scripts I've run into an interesting case where I need to generate the name of the group SQL Server creates for file system access. On my test SQL Server 2008 instance, that name is "SQLTBWS\SQLServerMSSQLUser$SQLTBWS$INST01". On my SQL Server 2005 default instance the name is "SQLTBWS\SQLServer2005MSSQLUser$SQLTBWS$MSSQLSERVER". I want to be able to dynamically generate the name for the specific instance I'm working on.

I first load variables with the version, the machine name, and the instance name. (In my example these are hard-coded for the sake of brevity.) I then use the back-tick (`) character in the string as an 'escape' character, to say that the embedded dollar sign characters are not variables to be substituted. I also had to use one after the version variable to indicate that the next character was not part of the variable. Here's the code:

$vers = 'SQLServer'
$machnm = 'SQLTBWS'
$instnm = 'INST01'

$grpnm = "$machnm/$vers`MSSQLUser`$$machnm`$$instnm"

The $grpnm variable now contains the name of the group SQL Server uses to access the file system directories where its objects live.

Allen