THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

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.


Published Wednesday, March 30, 2011 12:41 PM by AllenMWhite
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement