List all SQL Server-Related Service Accounts using WMI under PowerShell

Ever wanted to know which service accounts SQL Server is using?

Fast PowerShell WMI Method(doesn’t work with boxes running Windows 2000)

1. Make a text file containing all the machine names of your SQL Servers – don’t worry about multiple instance names or virtual names, just put the machine names, one per line.  This file will be used to populate the service account info once for each server listed.

2. Create a text file with the following, and save it as  get-sql-svcaccounts-wmi.ps1 to be run in PowerShell.

$erroractionpreference = "SilentlyContinue"
foreach ($server in get-content("2009-10-27-sql-servers.txt"))
     {
     
     $class = Get-WmiObject -computername $server -namespace root\Microsoft\SqlServer\ComputerManagement -class SQLService
     "`n" + $server + ":"
     foreach ($instance in $class) {$instance.HostName + ", " + $instance.ServiceName + ", " + $instance.StartName}
     }

Why silently continue in case of error?  Well that’s for those lovely Windows 2000 boxes that are _still_ on your network.

Here’s a screenshot:

Slow PSSERVICE + PowerShell Method(works with boxes running Windows 2000 and later OS versions of Windows)

Here’s a PSSERVICE.EXE + PowerShell script that will work with Windows 2000 boxes.  The script builds a PSSERVICE string & runs the string using Invoke-Expression and passing the prefix of each service name.

1. Make a text file containing all the machine names of your SQL Servers.

2. Create a text file with the following, and save it as   get-sql-svcaccounts.ps1 to be run in PowerShell.

foreach ($server in get-content("2009-10-27-sql-servers.txt"))
     {
     $exp1="psservice \\"
     $exp2=" security MSSQL | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     
     #debug
     #echo $cmd_line
     
     "`n" + $server + ":"
     Invoke-Expression $cmd_line
     #part 2
     $exp2=" security MSDts | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 3
     $exp2=" security MSFTE | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 4
     $exp2=" security SQL | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 5
     $exp2=" security Report | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
     
     #part 6
     $exp2=" security MSOLAP | Select-String -pattern DISPLAY_NAME, SERVICE_NAME, ACCOUNT"
     $cmd_line = $exp1 + $server + $exp2
     Invoke-Expression $cmd_line
          
     }

Yes, I realize the above could be much shorter – if you rewrite it I’ll edit this post and give you credit.

Here’s a screenshot of the PSSERVICE approach:

That’s it!

References:

http://xinn.org/wmic.html


http://www.eggheadcafe.com/software/aspnet/30426636/change-service-account-pa.aspx


http://www.mattwilsoninc.com/microsoft/the-many-ways-to-control-windows-services/


http://www.myitforum.com/articles/40/view.asp?id=12441

T-SQL to generate all rights for the current database as valid T-SQL statements

Tested on SQL Server 2005 & SQL Server 2000
I wrote up a post on sp_helprotect2 previously, but I like this method better, even though they both use stored procedure sp_helprotect to populate the table.

      
--T-SQL to generate all rights for the current database in valid T-SQL statements  
create table dbo.##protect ( 
            [owner] sysname,
            [object] sysname, 
            [grantee] sysname, 
            [grantor] sysname, 
            [protectype] sysname, 
            [action] sysname, 
            [column] sysname 
) 
go 

insert into dbo.##Protect
exec sp_helprotect 
go 
      
select protectype + action + ' ON ' + object + ' TO [' + grantee + ']' 

from dbo.##protect 
where object <> '.' 
order by action 
go 
      
drop table dbo.##protect 
go 

What’s my NTFS Cluster Size?

Maybe you’re a SQL Server DBA and you prefer 64K cluster sizes for optimizing disk I/O to read an extent at a time, or you just need to know how a volume was formatted.  The command line can help using fsutil:

fsutil fsinfo ntfsinfo c:  

Sample output:

“Bytes per cluster” shows the cluster size in use on the volume letter specified – in this case, 65536 bytes, or 64KB.

Cue Dora the Explorer “We Did It” music.

References:

http://www.tech-archive.net/Archive/WinXP/microsoft.public.windowsxp.basics/2008-06/msg00172.html


http://www.sqlservercentral.com/Forums/Topic242971-65-1.aspx#bm243468

What’s gaffer’s tape? Volunteering at the 2009 Utah Open Source Conference

Last Wednesday night I went to the Miller Free Enterprise Center on the Sandy Campus of Salt Lake City Community College to help set up for UTOSC 2009 – this year’s Utah Open Source Conference.  My first marching orders from a seasoned network admin were to cut and crimp some cables for the exhibitor booths/tables.  Turns out there is a best way to tape cords and cables to the floor, and it involves a specific kind of tape that techs who accompany concert tours and assemble movie sets use: it’s called gaffer’s tape.  Gaffer’s tape tears like cloth tape, but is much stronger, and unlike duct tape, leaves no adhesive residue behind when removed.  After false starts at Home Depot & Graybar, I purchased a 60yd roll for $20(after taxes) at the local Guitar Center.  Usually it comes in matte black as to not reflect any light, but the roll I purchased was white for high visibility(cords are a trip hazard, even when taped down).  Cheap imitations of gaffer’s tape exitst, but if you paid less than $15 a roll it was likely not the kind of tape a gaffer on a film crew would use.  Graybar sold me something similar called Electrician’s Bundling Tape, but it didn’t tear nearly as well, and cost around $7 per roll.

Mounting tape was another adhesive used in conference setup.  I needed to tape an additional Cisco wireless access point(conference attendees were saturating the wireless spectrum with their array of RF devices – I think we added 4 APs to the venue) to a glass window, and this mounting tape worked so well I wonder how somebody managed to remove the equipment afterward.  A $500 radio within arm’s reach might be in danger of being stolen, and any safeguard preventing a casual thief from walking away with this hardware seemed a judicious choice.

Next year I want to be able to attend more presentations, so in the future think I’ll limit my volunteer efforts to the night before the event.  Presenters were very technically oriented, and the raw knowledge disseminated more than made up for an occasional lack of salesman-like polish in delivery.  As a geek I appreciate more substance and passion than hollow style.  When a fellow geek noticed I was running Windows 7 on my laptop and quipped regarding the same, a guy I didn’t know across the table from me shot back, “Open Source is more than Linux”.

UTOSC speakers had a focus on automation and uptime – the theme of the conference was (afford | scal | reli)ability.  Another attendee remarked at dinner that in his experience, *nix admins tended to focus more on automation of processes than their Windows counterparts.  Or as another presenter, Christer Edwards, put it, “A good admin is a lazy admin.”  I agree.  Next year I’m going to offer to present on automating processes within - you guessed it – Windows.

SanDisk Sansa Clip Survives Washing Machine 1000 RPM Journey in Pant Pocket

I thought my precious 2GB Sansa Clip MP3 player was destroyed when I found it had gone through our Fisher & Paykel washing machine.  I let it dry out for a few days in a bag of uncooked rice.

When I finally powered on the Sansa Clip the screen lit up, but nothing legible was displayed.  It was as if all the pixels were turned on but were very dim.  Then the device powered off.  I thought the display was shot.

2 months later my son convinced me to recharge the device.  I was able to read files from the device, but when I turned it on, the display problem persisted.  Only this time, when all the pixels lit up they were noticeably brighter.  I looked in the Sansa manual to find ways blindly navigate the controls, and found an option to reset the player by holding the switch in the powered off position for 15 seconds, so I tried that instead.

When I again turned on the Clip, the display worked!  The device had survived with no apparent ill effects, other than needing a reset.  I had to take a picture.

This thing is small and tough, and unlike the Apple ipod firmware, it plays Ogg Vorbis(some of my preferred podcasts are in that format).  Thank you SanDisk engineers!