Get SQL Server Service Accounts with WMI

Today I was tasked with retrieving the SQL Server Service accounts to validate them in Active Directory.  I wanted a simple way to get them and show the account names for each SQL Server service.  I knew that you could get the service in PowerShell with Get-Service.  I also new that PowerShell could get the service with Get-WmiObject –class Win32_Service. But neither one had the ServiceAccount property.

So I turned to WMI under SMO with PowerShell.  The script below has the details of a script I named GetSqlServices_WMI with a parameter of $sqlserver.

param (

# Call this with .filename.ps1 "servername"

$wmi = New-Object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $sqlserver
$wmi.Services | Select DisplayName, Type, StartMode, ServiceState, ServiceAccount | ft -auto

The call is in the code.  The results will have DisplayName, Type, StartMode, ServiceState and ServiceAccount listed in a table with column headers.

Great way to interrogate the Services from SQL Server.

Available Properties to you are as follows:

  • Parent
  • AcceptsPause
  • AcceptsStop
  • Description
  • DisplayName
  • ErrorControl
  • ExitCode
  • PathName
  • ProcessId
  • ServiceAccount
  • StartMode
  • Type
  • IsHadrEnabled (because I am using 11.0 SMO)
  • StartupParameters
  • Dependencies
  • AdvancedProperties
  • Urn
  • Name
  • Properties
  • UserData
  • State

With Methods

  • Alter
  • ChangeHadrServiceSetting (because I am using 11.0 SMO)
  • ChangePassword
  • Equals
  • GetHashCode
  • GetType
  • Initialize
  • Pause
  • Refresh
  • REsume
  • SetServiceAccount (handy little guy)
  • Start
  • STop
  • ToString
  • Validate

The link to the documentation for this object is here (Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer).

Enjoy and happy scripting.


Read More

SMO and Index Fragmentation

When working with SMO and Indexes there are times that you want to get the fragmentation of this particular index.  In SMO there is a method on the Index called EnumFragmentation.  When you call this method, there is a bug in SMO out on Connect (SMO Index Enum Fragmentation…) that you should vote for.  This is a very impactful bug as this is the only way in SMO to get the Fragmenation information on an Index.

The bug is this.  When you call EnumFragmentation() you get the entire Database fragmentation by index instead of for the Index that you are calling it from.  Normally what you would expect is to see

select *
from sys.dm_db_index_physical_stats(9, 39399393, NULL, NULL, 'SAMPLED')

Instead you see

select *
from sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED')

This is crazy as you only get a limited set, which I know you can change with the method above by specifying the type of Detail (‘DETAILED’, ‘SAMPLED’, ‘LIMITED’) but it defaults to LIMITED.

Vote up the bug if this is something that affects you and your work.

Read More

Networking: What exactly is it, and how do you use it (appropriately)?

I have had the opportunity lately to tap into “Marketing” something to get some traction on a Vote for Me scenario.  It made me wonder about Networking and what it really is and what I would use it for.  There are many spammers and others out there who would just send anything and everything out to the masses in the name of Marketing.  I thought about this concept and this contest for a Cute Girls Hairstyle blog, that I am trying to get people driven to. This a neighbor of mine and I think that it is a nice gesture to support their cute family in this contest.

Now when people think of Marketing, that word, in and of itself can make you think of many things about what it is.  That is why I wanted to write this post about Networking.

When someone wants to “tap his network”, what kind of things are fair game?  I am part of many networks, LinkedIn, FaceBook, Microsoft MVP, Twitter, Blogging and some others.  Now when you think of the purpose of those networks, are there topics in them that are taboo?  If you opened the floodgate on them, would you have problems with complaints?  What exactly is “tapping the network” in these cases?  Many could successfully argue about LinkedIn being more professional contacts, Facebook being highly social and only that, MVP being Technical and Twitter being a hybrid of all those.  So how do you use them to get traction on something of a cause or contest or whatever, that you feel strongly about helping out with?  Would you cry foul if this came over a professional networking site?  Maybe, maybe not.

Here are my views, and I encourage you to weigh in on this topic, because it would be interesting to compile thoughts and post them about how this affects the online universe we all live in every day.

I think that networks are a great tool for finding jobs, friends, viewpoints and information about topics.  Each one seems to have a purpose, and I think it would be out of line to violate the purposes of those networks. A list of purposes are below and what I think they are used for.

  • Professional networks are mainly used to connect people in like professions for a few reasons.  The first reason I think of is needing help with problems that you just need another set of eyes.  This can get you in trouble a little in the IT world as it could be construed as “free consulting”, but could be valuable to both parties, so you could choose to overlook or to scold in this one. 
  • The next reason would be Job Search and contacting about jobs. This is a tricky one because of the recruiting that goes on.  Recruiters get in your network, and yet they are not technical like, at least in most cases.  So do they belong?  The job potential may be one that you could overlook their involvement in your network at that level, but I am not sure that it is the right networking opportunity and should be shied away from.
  • People connections.  This one can be done on many networking sites, and is probably appropriate on the majority of networks.  But at the same time, probably the most tricky, because of the boundaries of the above networks, when your topic is not in harmony with the purpose of the networks.  You can be friends with many in the professional networks, but how would your message be received about this contest above that I have pointed to?  This is tricky, is the best answer for me to this one.
  • Association networking.  You belong to a group or an association and you want to use this network for the above purpose.  This is probably out of line here as well, due to the charter of the organization or association.

With these thoughts in mind, I just find it frustrating that there is not a better way than Facebook to communicate out something that you want to get people involved in.  Now there is one network that I neglected to put down for a reason, and that is because it deals with a valuable sanctuary that most if not all of us have, and that is the “Email Box”.  Because of all the spam we get out there, it seems that the email box is being protected and is not seen at first as a networking method.  I just have been having a dilemma on how to get the word out to people that I know would vote or participate, but being overly respectful of all the requests for our time that we have in this ever changing world.  As we get more fast-paced, and crazy with time constraints, it becomes easy to have 5+ networks and to not be sure how to use each one of them appropriately.

For this contest, I have turned to Facebook, Blogging, LinkedIn as a status update instead of a mass message, Twitter as a plea for assistance, and I will turn to my family on Email and certain others that I know would not be offended in this venture.  What do you all think?  Where would this contest be appropriately submitted for help to get votes?  What are networks used for in your opinion?  And a topic for another day, how do you build networks to a level of effectiveness to assist you when you are in need of assistance?  I am sure that I have not done this topic justice, but wanted to get it out there so that I can continue to think about it, but get some weigh-in about your thoughts about “Networking” as it has been very much on the forefront of my mind lately with requests to “tap into my network” for assistance with a myriad of things.

Thanks for listening.

Read More

Powershell, SMO and Database Files

Powershell, SMO, SQL Server

In response to some questions about renaming a physical file for a database, I have 2 versions of Powershell scripts that do this for you, including taking the database offline and then online to make the physical change match the meta-data.

First, there is an article about this at  This explains that you start by setting the database offline, then alter the database and modify the filename then set it back online.  This particular article does not show the physical renaming of the file, but it is necessary since the ALTER DATABASE MODIFY FILE statement only changes the metadata of the database in master.

There is a procedure to do this with SQL CLR and TSQL, but I chose to illustrate it in Powershell and SMO (using SQL Provider as well as straight SMO).  The SQLCLR version is by a SQL Server MVP, Ted Krueger (Blog | Twitter) and can be found at this link.

My version will be based on Powershell and SMO with a smattering of SQL Provider provided in SQL 2008+.

Code Snippet
  1. Add-PSSnapin SqlServerCmdletSnapin100
  2. Add-PSSnapin SqlServerProviderSnapin100
  4. $servername = "localhost"
  5. $instance = "default"
  6. $dbname = "N2CMS"
  7. $logicalName = "N2CMS"
  8. $NewPath = "c:\program files\microsoft sql server\mssql10_50.mssqlserver\mssql\data"
  9. $NewFilename = "N2CMS_4.mdf"
  11. if(Test-Path "sqlserver:\sql\$servername\$instance\databases\$dbname")
  12. {
  13.         $database = Get-Item("sqlserver:\sql\$servername\$instance\databases\$dbname");
  15.         $fileToRename = $database.FileGroups["PRIMARY"].Files[$logicalName]
  16.         $InitialFilePath = $fileToRename.FileName
  18.         $fileToRename.FileName = "$NewPath\$NewFilename"
  20.         $database.Alter();
  21.         $database.SetOffline()
  22.         Rename-Item -Path $InitialFilePath -NewName "$NewFilename"
  23.         $database.SetOnline()
  24.         Write-Host "File Renamed"
  25. }
  26. else
  27. {
  28.         Write-Host "Database does not exist";
  29. }

If you notice, I first add the Snapin that is the SQL Provider.  If you already have it loaded in your Powershell Profile, then you can just omit those lines.  If you do not have SQL 2008 objects installed on this machine, then you will notice that the Snapins don’t load either.  In that case you would just use the version below.

In the SQL Provider version you will see a Powershellism with Test-Path and you are using a path, it just is a SQL provider path that points to the Database and makes sure that it exists.  Compare it to the other version where you are looking at the Server.Databases[$dbname] and then you get the filegroups and then the files.

This version of the script is purely SMO and PowerShell.  First you load the objects from SQL Server 2005/2008 SMO.

Code Snippet
  1. # Always
  2. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | outnull
  4. # Only if you don't have SQL 2008 SMO Objects installed
  5. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | outnull
  7. # Only if you have SQL 2008 SMO objects installed
  8. [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum") | outnull
  10. $servername = "localhost"
  11. $instance = "default"
  12. $dbname = "N2CMS"
  13. $logicalName = "N2CMS"
  14. $NewPath = "c:\program files\microsoft sql server\mssql10_50.mssqlserver\mssql\data"
  15. $NewFilename = "N2CMS_4.mdf"
  17. $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $servername
  18. if($server.Databases[$dbname] != $null)
  19. {
  20.         $database = $server.Databases[$dbname];
  21.         $fileToRename = $database.FileGroups["PRIMARY"].Files[$logicalName]
  22.         $InitialFilePath = $fileToRename.FileName
  23.         $fileToRename.FileName = "$NewPath\$NewFilename"
  24.         $database.Alter();
  25.         $database.SetOffline()
  26.         Rename-Item -Path $InitialFilePath -NewName "$NewFilename"
  27.         $database.SetOnline()
  28.         Write-Host "File Renamed"
  29. }
  30. else
  31. {
  32.         Write-Host "Database does not exist";
  33. }

This version will use the Server object to get the database that you are looking for so that you can get at the files.  The file you are looking for is the $logicalName so that you can get the PhysicalName of the file.  Then using the builtin CmdLets to handle Files operations, a Rename-Item is issued to rename the initial path to the new one.

Operation order:

  • Rename-Item from old to new

I did not put these in a function, but they could easily (or not too hard that is) put together using Parameters and make it versatile for other situations, but that is for you to do so that it works for your scenario.

Love PowerShell!

Read More

An honor to be among the SQL Server MVPs

I just found out last night that I was officially awarded SQL MVP.  I am honored to be among those that I respect and admire. 

I don’t contribute for the recognition and as a former MVP Lead I know the caliber of those that contribute to the SQL Server community, and even those that are not MVP, I am grateful for those of you who have helped me get where I am in SQL Server land.

I just wanted to say thanks to the SQL Community for the support and also to Microsoft for this award.

Here’s to a great 2011 and hope to see you MVPs at the Summit in February.
Read More

I am loving Office 2010

I have installed Office 2010 and found that Office OneNote is included in the Office 2010 Professional Plus. It is a very nice Suite of products and is very clean and polished.

The thing I love most is the improved version of Outlook. It is now in the same realm as the other products in the Office Suite. I am anxious to use the Powerpoint 2010 to do a presentation next Monday.

Happy trails.

Read More

The Best Thing I Learned at PASS

SQL Server

Being that I have never missed a PASS Summit yet (in the US), I thought I would share my experience about last PASS and previous PASS conferences.

I attended a Pre-Con with Kalen Delaney on Query Tuning and Plan Guides. I also remember sessions last PASS that were very relevant to my job. Each year that I attend PASS, I find that there is more to know and there is more to learn from many that are there to present.

I was pleased that when I got home each time, I applied my learning directly to my job. I remembered the Plan Guides day, and ironically while troubleshooting some performance issues with a database, found that I could leverage plan guides for a specific query, and it worked as Kalen illustrated. It actually saved many queries from bloating the plan cache, and was pretty helpful. But I had not done anything at that time with Plan Guides, so the topic and the time spent was very timely.

The other topics included Powershell and Wait Stats and Wait Types. The more practical sessions are very helpful and very applicable to my job as a DBA and manager mentoring DBAs. I can only say that I still plan attending all the SQL PASS Summits in the US. One day I hope to attend one outside of the US, but for now, the value of these Summits in networking, vendor introductions and all out knowledge transfer is worth every minute spent for me.

Read More

Windows 7 is pretty cool

I have downloaded Windows 7 and installed the x64 version on my Dell Latitude D820 last week. My experience is this:

4 GB, Core 2 Duo 2.0 GHz, 200 GB – 7200 RPM drive.

Install: < 30 minutes from CD in drive to Login
Boot-up: < 20 seconds from Off to Login

The only thing I found wierd is that when I went to join my domain at home, I had to configure my network card to have the IP Address and to have the domain name as the default connection DNS suffix. Then I could join my domain, otherwise, it would give me an error and I could go no farther. (bug)

The other thing is that when I was away from my network and had no network connectivity, it logged in very fast (something that Vista is not doing for me).

The other things in the UI as far as grouping or Libraries, and the concept of a Home Network group was pretty cool.

Anyway, my experience has been good, I have installed Visual Studio 2008 with SP1 and SQL 2008 on the laptop and VS 2008 launches amazingly fast and SQL 2008 runs well too.
Overall, I give this a 9 for experience, as I have burned a DVD from ISO with no additional software or anything and it all worked. The only thing I wish that it has was the ability to browse an ISO and use it as a drive without another piece of software. I installed MagicDisc on the machine and it hung everything when I went to access the DVD ISO.

Have fun with it, I plan on taking it through the motions.

Read More

GP 10 and eConnect and RMTransaction


I have been working on a project that involves eConnect 10 for Microsoft Dynamics GP 10.  The documentation exists, but is very sparse when it comes to specifics about using the interface.

When using the RM transactions and distributions, the first bite that we had was that there is a property in the taRMTransaction called CREATEDIST and by default it is set to 1 and you need to ensure that it is set to 0 if you are creating your distributions yourself, which in this case I am.

More to follow on the process of this so that there is at least some documentation on the web about how to do it entirely.

Read More