PowerShell Power Breakfast in Utah – Kickoff Meeting 8:00am-9:00am, Wednesday, November 25, 2009

Buy your breakfast at the East Bay Cafe, have a seat, and learn about PowerShell for one hour.  That’s the basic premise of the monthly PowerShell Power Breakfast.  PowerShell provides for Windows what shells like Bash provide in Linux – a command line for one-off tasks or automation, be they simple or complex.  Schedule this event on your calendar, and bring your manager and/or coworkers.

I’ll be presenting a rapid-fire introduction to PowerShell, and will be followed by Ben Miller, current president of the Salt Lake City SQL Server Users Group.  Ben will present on Gathering SQL Server Performance Data With PowerShell.  Both presentations will be brief, and if time allows, we’ll hold a free-form discussion afterward.

Schwag will be given to a few lucky attendees, including this OGIO backpack, courtesy of Redmond and local Microsoft Technology Strategist Dan See.  Thanks, Dan!

The following screen shot shows a directory listing(yes, that ls alias is installed by default), retrieval of BIOS information, and password generation using a .NET method, all using PowerShell.

Did that pique your interest?  Come to the breakfast to learn more!

Windows PowerShell Quick Reference

Dig Out by Digging Into PowerShell

The PowerShell Power Breakfast is open to any individual interested in Windows PowerShell, from veteran developers and sysadmins to those just starting with automation & scripting in a Windows context.

Where: Novell Cafeteria, Building G, Provo Campus (map)

Directions: Take the University Ave exit off I-15, cross University Ave, and turn left (north) onto Novell Place and enter the Novell campus. When you drive up to bldg H (the 8-story bldg), turn left and park in the SW parking lot. The sidewalk on the west side of bldg H will take you to the cafe (bldg G). We'll be in the conference room at the far north end (past the food court).

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