Category Archives: Powershell

SQL Server and PowerShell (SQLPS) Starter

The following two snippets of code are two ways to achieve the same outcome, which is the $Server object containing the default instance.

$Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)";

Or when opening a SQL PS (Powershell) prompt at the default location. e.g. PS SQLSERVER:\SQL\SB01\DEFAULT>

$Server = (Get-Item .)

I mention this because I was asked what the simplist entry point to PowerShell for SQL person was. I can think of nothing simpler than opening the SQL PS shell from SSMS and typing the folllowing:

(gi .)

Note that gi is shorthand for Get-Item.
For example now that we have a reference to out instance we can then start to explore.

(gi .).Databases.FileGroups.Files|Select @{Name="UsedSpace_MiB";Expression={($_.UsedSpace/1KB)}},Name

 

(gi .).Databases|Select Name,LastBackupDate

 

(gi .).Databases

 

(gi SQLSERVER:\SQL\$ENV:ComputerName\DEFAULT).Databases

Find SQL Server Instances

Any environment will kick up the odd surprise with extra servers you did not know about (suddenly appearing), which is why I always like to have a look around the environment every so often to see what is there. So how do you find SQL instances and without a 3rd party tool?

SQL Command Line

Luckily you can do this via the built-in query tools that Microsoft provides.

Depending on your version of SQL, the command line entries will look like this:

ISQL -L
OSQL -L
SQLCMD -L

Finding Servers that might have SQL

Active Directory

Some companies like to put “SQL” in to the name of the server, or in the description field. So we can look for those with this:

Import-Module ActiveDirectory;
Get-ADComputer -Filter 'Name -like "*sql*" -or Description -like "*sql*"' -Properties Description | Select-Object @{Name="ServerName";Expression={$_.Name}},@{Name="InstanceName";Expression={$null}};

While this only gets a list of probable servers with SQL instances we can use this to scout for the actual installs.

ManagedComputer

Using ManagedComputer only requires a computer name and it will return the instances it knows about. This can also easily be combined with the AD search shown above.

[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null;
$Computer = $Env:COMPUTERNAME
$ManagedComputer = New-Object "Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer" $Computer;
$ManagedComputer.ServerInstances | Select-Object @{Name="ServerName";Expression={$Computer}},@{Name="InstanceName";Expression={$_.Name}};

SqlDataSourceEnumerator

GetDataSources is very easy and only requires a simple call to return all the instances which it can see.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()

I tend to use this version below as I only want server and instance name.

[System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources() | Select-Object @{Name="ServerName";Expression={$_.ServerName}},@{Name="InstanceName";Expression={$_.InstanceName}};

Sledgehammer

The final option is to check if the SQL service(s) are installed. Which is basically the go and have a look everywhere option.

Import-Module ActiveDirectory;

ForEach ($Computer in Get-ADComputer -Filter 'Name -like "*"'){
$ComputerName = $Computer.Name;
$Ping = Get-WmiObject Win32_PingStatus -Filter "Address='$ComputerName'";

 IF($Ping.StatusCode -eq 0)
 {
  Get-WmiObject -class "Win32_Service" -Computer $ComputerName|?{$_.Name -like "*MSSQL*"}|Select @{Name="ServerName";Expression={$ComputerName}},@{Name="InstanceName";Expression={$_.Name}};
 }
}

Finally…

Sometimes an instance will just refuse to show up… This is for a variety reasons such as firewalls, permissions, network issues…and sometimes they are just hiding.

If you want something that is a more complete solution with regards to searching and presentation then you may want to go and look at Microsoft Assessment and Planning (MAP) Toolkit.

Quick data wipe via truncate

“How to remove all data from a database?” is something that I have often seen asked on the forums. How do you remove all the data quickly? First you try with delete and find that this is too slow and the transaction log has to record all the changes, so you use truncate which is great at removing the data and with less logging. However the problem with this is that you cannot use truncate when there are constraints.
On a side note I often say that it is best to have a scripted empty version of your database rather that remove data.

You will need SMO installed for this script to work.

This script will produce the commands to:
1. Drop Constraints
2. Truncate All Tables
3. Recreate Constraints
It is not fully automated for safety and relies on the end user checking that the scripts perform the tasks they want!
Replace (local) and AdventureWorks with the sql instance/database you want to script the changes for, and as long as you have not got any schema bound objects then you will have a script to accomplish the task.

!!!PLEASE CHECK!!! THIS WILL PRODUCE SCRIPTS THAT DELETE DATA & DROP/CREATE CONSTRAINTS!!!

0. Backup taken!!!
1. Correct Server/Instance selected?
2. Correct Database selected?

I take no Liability for any issues relating to this script, but appreciate any feedback.

#http://jongurgul.com/blog/quick-data-wipe-truncate/
[Void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
[Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions";
$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105
$ScriptingOptions.ContinueScriptingOnError = $false; #ignore scripts errors, advisable to keep set to $false
$SqlInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" "(local)"; 
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject");
$SqlInstance.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject");
$Database = $SqlInstance.Databases["AdventureWorks"];
$DropFKScript = $null;
$TruncateScript = $null;
$CreateFKScript = $null;
$SchemaBound = $($Database.Views|Where-Object {!$_.IsSystemObject -and $_.IsSchemaBound}|%{$_});
cls;
If ($SchemaBound -eq $null){
	$Database.Tables|%{$_.ForeignKeys}|%{
	$ScriptingOptions.DriForeignKeys = $true;
	$ScriptingOptions.SchemaQualifyForeignKeysReferences = $true;
	$ScriptingOptions.ScriptDrops = $false;
	$CreateFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	$ScriptingOptions.ScriptDrops = $true;
	$DropFKScript += "{0}`r`nGO`r`n" -f $($_.Script($ScriptingOptions));
	}
	$TruncateScript += $Database.Tables|Where-Object {!$_.IsSystemObject}|%{"TRUNCATE TABLE [{0}].[{1}]" -f ($_.Schema,$_.Name)};
}
Else{
	Write-Host "Schema Bound Objects Present:";
	$SchemaBound|%{"[{0}].[{1}]" -f ($_.Schema,$_.Name)};
}
$DropFKScript;
$TruncateScript;
$CreateFKScript;

Get-VolumeFreeSpace

Here is an example function to return free space remaining on a volume with associated details. Updated 27/04/2012 to fix mount points.

Usage Examples:
Get-VolumeFreeSpace|ft
Get-VolumeFreeSpace|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft
Get-VolumeFreeSpace|Where-Object {!$_.SystemVolume}|Select ComputerName,Name,MountPoint,FreeSpaceGiB|ft
Get-VolumeFreeSpace “ComputerA”,”ComputerB” |ft

#http://jongurgul.com/blog/get-volumefreespace/
Function Get-VolumeFreeSpace{ 
    Param([String[]]$Computers) 
    If (!$Computers) {$Computers = $ENV:ComputerName} 
    $Base = New-Object PSObject; 
    $Base | Add-Member Noteproperty ComputerName -Value $Null; 
    $Base | Add-Member Noteproperty DeviceID -Value $Null; 
    $Base | Add-Member Noteproperty SystemVolume -Value $Null; 
    $Base | Add-Member Noteproperty DriveType -Value $Null; 
    $Base | Add-Member Noteproperty Name -Value $Null; 
    $Base | Add-Member Noteproperty MountPoint -Value $Null; 
    $Base | Add-Member Noteproperty FreeSpaceGiB -Value $Null; 
    $Results =  New-Object System.Collections.Generic.List[System.Object]; 
 
    ForEach ($Computer in $Computers){ 
        $Volume = Get-WmiObject -Class "Win32_Volume" -ComputerName $Computer; 
        $MountPoint = Get-WmiObject -Class "Win32_MountPoint" -ComputerName $Computer | Select @{Name="DeviceID";Expression={$_.Volume.ToString().Substring($_.Volume.ToString().IndexOf("`"")).Replace("`"","").Replace("\\","\")}},@{Name="MountPoint";Expression={$_.Directory.ToString().Substring($_.Volume.ToString().IndexOf("`"")).Replace("`"","").Replace("\\","\")}};   
        [String[]]$Mounts = $MountPoint|%{$_.DeviceID} 
        $Volume |%{ 
        $Entry = $Base | Select-Object * 
        $Entry.ComputerName = $Computer; 
        $Entry.DeviceID =  $_.DeviceID; 
        $Entry.SystemVolume =  $_.SystemVolume; 
        $Entry.DriveType = $_.DriveType; 
        $Entry.Name = $_.Name; 
        $Entry.FreeSpaceGiB = [Math]::Round($_.FreeSpace/1GB,3); 
        $DeviceID = $_.DeviceID;         
        $MountPoint| Where-Object{$DeviceID -contains $_.DeviceID}|%{
        $Local = $Entry | Select-Object *
        $Local.MountPoint = $_.MountPoint;
        [Void]$Results.Add($Local);
        }; 
        $_|Where-Object{$Mounts -notcontains $_.DeviceID}|%{[Void]$Results.Add($Entry);}; 
        } 
    }     
    $Results 
}

Dell Service Tag

 

The Dell soap API now returns a HTTP Error 503. The service is unavailable so the code listed below no longer functions.

Dell provide an alternative method to retrieve the information via a REST API. Please search for Dell Support Services APIs for more details.

A simple call can be made via the following link: (Note that you will need to register with Dell to get an apikey to use this service.)

https://api.dell.com/support/v2/assetinfo/header/tags.xml?svctags={svctags}&apikey={apikey}

Here is an example of how to call the Dell Service Tag GetAssetInformation web service so that you can retrieve information about your server. It simply returns the results in an xml format, however if anyone has any ideas or requests please feel free to ask.

updated:
http://xserv.dell.com/jigsawwebservices/AssetService.asmx?WSDL changed to http://xserv.dell.com/services/AssetService.asmx?WSDL

Usage Examples:
Get-DellAssetInformation
Get-DellAssetInformation “MyTag”



Function Get-DellAssetInformation{
Param([String]$ServiceTag = $(Get-WmiObject -Class "Win32_Bios").SerialNumber);
	Try{
	$AssetService = New-WebServiceProxy -Uri "http://xserv.dell.com/services/AssetService.asmx?WSDL";
	$ApplicationName = "AssetService";
	$Guid = [Guid]::NewGuid();
	$Asset = $AssetService.GetAssetInformation($Guid,$ApplicationName,$ServiceTag);
	$Writer = New-Object "System.IO.StringWriter";
	$XmlSerializer = New-Object System.Xml.Serialization.XmlSerializer($Asset.GetType());
	$XmlSerializer.Serialize($Writer,$Asset);
	[String]$Result = $Writer.ToString();
	$Writer.Flush();
	$Writer.Close();
	Return $Result;
	}
	Catch{
	Write-Host $($_.Exception.Message);	
	}
}

 

 

Get-DatabaseSizes

Simple example using SMO to get database storage information. You will need SMO installed for this script to work.

Note Size relates to the file size on disk, where as space refers to the storage used or available.

Usage Examples:

Get-DatabaseSizes
Get-DatabaseSizes . | Select *
Get-DatabaseSizes . | Select DatabaseName,LogicalName,FileName,Size_MiB
Get-DatabaseSizes . | Select FileName,Size_MiB | Format-Table
Get-DatabaseSizes . | Where-Object {$_.DatabaseName -like “m*”}

#http://jongurgul.com/blog/get-databasesizes/
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null;
Function Get-DatabaseSizes([String] $SQLInstanceName = $Env:COMPUTERNAME)
{
 $SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLInstanceName;
 $Results =  New-Object System.Collections.Generic.List[System.Object];
 ForEach ($db in $SQLInstance.Databases)
 {
  ForEach ($fg in $db.FileGroups)
  {
  $Results += $fg.Files | Select-Object @{Name="DatabaseName";Expression={$db.Name}},
  @{Name="FileGroupName";Expression={$fg.Name}},
  @{Name="SpaceUsed_MiB";Expression={([Math]::Round($_.UsedSpace/1KB,3))}},
  @{Name="AvailableSpace_MiB";Expression={([Math]::Round(($_.Size-$_.UsedSpace)/1KB,3))}},
  @{Name="Size_KiB";Expression={$_.Size}},
  @{Name="Size_MiB";Expression={([Math]::Round($_.Size/1KB,3))}},
  @{Name="Size_GiB";Expression={([Math]::Round($_.Size/1MB,3))}},
  @{Name="Size_bytes";Expression={($_.Size*1024)}},
  @{Name="AutoGrowth";Expression={if($_.GrowthType-eq "Percent"){$_.Growth.ToString()+" %"}else{([Math]::Round($_.Growth/1KB,3)).ToString()+" MiB"}}},
  @{Name="MaxSize";Expression={($_.MaxSize)}},
  @{Name="FileType";Expression={"ROWS"}},
  @{Name="IsOffline";Expression={($_.IsOffline)}},
  @{Name="IsReadOnly";Expression={($_.IsReadOnly)}},
  @{Name="LogicalName";Expression={($_.Name)}},
  @{Name="FileID";Expression={($_.ID)}},
  @{Name="FileName";Expression={($_.FileName.Substring($_.FileName.LastIndexOf("\")+1))}},
  @{Name="Path";Expression={($_.FileName)}}
  }
  $Results += $db.LogFiles| Select-Object @{Name="DatabaseName";Expression={$db.Name}},
  @{Name="FileGroupName";Expression={$null}},
  @{Name="SpaceUsed_MiB";Expression={([Math]::Round($_.UsedSpace/1KB,3))}},
  @{Name="AvailableSpace_MiB";Expression={([Math]::Round(($_.Size-$_.UsedSpace)/1KB,3))}},
  @{Name="Size_KiB";Expression={$_.Size}},
  @{Name="Size_MiB";Expression={([Math]::Round($_.Size/1KB,3))}},
  @{Name="Size_GiB";Expression={([Math]::Round($_.Size/1MB,3))}},
  @{Name="Size_bytes";Expression={($_.Size*1024)}},
  @{Name="AutoGrowth";Expression={if($_.GrowthType-eq "Percent"){$_.Growth.ToString()+" %"}else{([Math]::Round($_.Growth/1KB,3)).ToString()+" MiB"}}},
  @{Name="MaxSize";Expression={($_.MaxSize)}},
  @{Name="FileType";Expression={"LOG"}},
  @{Name="IsOffline";Expression={($_.IsOffline)}},
  @{Name="IsReadOnly";Expression={($_.IsReadOnly)}},
  @{Name="LogicalName";Expression={($_.Name)}},
  @{Name="FileID";Expression={($_.ID)}},
  @{Name="FileName";Expression={($_.FileName.Substring($_.FileName.LastIndexOf("\")+1))}},
  @{Name="Path";Expression={($_.FileName)}}
 }
 RETURN $Results
}

http://gallery.technet.microsoft.com/scriptcenter/da2de04e-4f3d-4a84-8e2a-7950b562cb84

Find Uptime / Last Boot Up Time Commands

There are numerous ways to find how long a machine has been powered on for, and below I will list a few of them:

CMD

systeminfo | find "System Boot Time"
net statistics server | find "Statistics since"
net statistics workstation | find "Statistics since"

Powershell

$LastBootUpTime = [System.Management.ManagementDateTimeConverter]::ToDateTime($(gwmi Win32_OperatingSystem).LastBootUpTime)
$d = New-Timespan $LastBootUpTime (Get-Date)
"{0} days {1} hours {2} minutes" -f $d.days,$d.hours,$d.minutes

Get-StringHash and Get-FileHash

Hashing

Here are two of my powershell scripts that provide a quick and easy way to hash either a string or a file using any of the cryptography hash algorithms.

Get-StringHash

#http://jongurgul.com/blog/get-stringhash-get-filehash/
Function Get-StringHash([String] $String,$HashName = "MD5")
{
$StringBuilder = New-Object System.Text.StringBuilder
[System.Security.Cryptography.HashAlgorithm]::Create($HashName).ComputeHash([System.Text.Encoding]::UTF8.GetBytes($String))|%{
[Void]$StringBuilder.Append($_.ToString("x2"))
}
$StringBuilder.ToString()
}

Usage Examples:

Get-StringHash “My String to hash” “MD5”
Get-StringHash “My String to hash” “RIPEMD160”
Get-StringHash “My String to hash” “SHA1”
Get-StringHash “My String to hash” “SHA256”
Get-StringHash “My String to hash” “SHA384”
Get-StringHash “My String to hash” “SHA512”

http://gallery.technet.microsoft.com/scriptcenter/Get-StringHash-aa843f71

Get-FileHash

#http://jongurgul.com/blog/get-stringhash-get-filehash/
Function Get-FileHash([String] $FileName,$HashName = "MD5")
{
$FileStream = New-Object System.IO.FileStream($FileName,[System.IO.FileMode]::Open)
$StringBuilder = New-Object System.Text.StringBuilder
[System.Security.Cryptography.HashAlgorithm]::Create($HashName).ComputeHash($FileStream)|%{[Void]$StringBuilder.Append($_.ToString("x2"))}
$FileStream.Close()
$FileStream.Dispose()
$StringBuilder.ToString()
}

Usage Examples:

Get-FileHash “C:\MyFile.txt” “MD5”
Get-FileHash “C:\MyFile.txt” “RIPEMD160”
Get-FileHash “C:\MyFile.txt” “SHA1”
Get-FileHash “C:\MyFile.txt” “SHA256”
Get-FileHash “C:\MyFile.txt” “SHA384”
Get-FileHash “C:\MyFile.txt” “SHA512”

http://gallery.technet.microsoft.com/scriptcenter/Get-FileHash-83ab0189

SQL Server Instance Security: Scripting Permissions

Another How-To post and this time focusing on scripting permissions. Here is how to script User(s), Object(s), Role(s), Server Role(s) and Server Object(s) Permissions. Beginning with the simple way via SSMS, before showing a custom script solution that I have written.

Scripting SQL Server Security: Simple Solution

In order to script the security settings for an instance of SQL server via SSMS please follow:

Right Click on your Database, Tasks, Generate Scripts, Next,
“Select specific database objects” :-> ticking users and database roles,Next
,Advanced :-> change Script Logins to true, and Script Object Level Permissions to true. (Also you can change “Script for Server Version” if appropriate”)
,OK,Next,Next

However in the script created all SQL authentication accounts will not have been produced as expected and instead this message will be present:

/* For security reasons the login is created disabled and with a random password. */

Although this is not a massive problem as the passwords can be reset from a password store and the accounts enabled again.

Scripting SQL Server Security: Custom

This script was written initially to allow permissions to be scripted out with a single click, however I have written a small custom section to also retrieve SID and password hash values so SQL Authenticated accounts are scripted as I need them.

Finally I had to change it very slightly to make it work within a SQL Job which is probably why the code is a little sloppy in places (I will refactor this!), however it is now possible to run this on a schedule to a file. See Setting up a Job.

Note for SQL 2000

The scripter allows the specification of TargetServerVersion to script for, however because the security model is substantially different between SQL 2000 and the later versions there is no guarantee that the script will work 100%. (I also do not have access to SQL 2000 to test, sorry.)

$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105

In a scenario with SQL 2000 it would be best to follow this article from Microsoft:

How to transfer logins and passwords between instances of SQL Server

Finally please tell me if you spot a bug, as this is very much a first draft of this script.

#http://jongurgul.com/blog/sql-server-instance-security-scripting-permissions/
[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
$SQLInstanceName = $Env:COMPUTERNAME; #"ServerName\InstanceName"
$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLInstanceName;
$DatabaseNames = $null #@("master","etc") comma seperated List of databases or $null for all
$BatchSeperator = $null #$null for none, or "`r`nGO" for carriage return, line feed and batch seperator.

#Options
[Microsoft.SqlServer.Management.Smo.ScriptingOptions] $ScriptingOptions = New-Object "Microsoft.SqlServer.Management.Smo.ScriptingOptions";
$ScriptingOptions.TargetServerVersion = [Microsoft.SqlServer.Management.Smo.SqlServerVersion]::Version90; #Version90, Version100, Version105
$ScriptingOptions.AllowSystemObjects = $false
$ScriptingOptions.IncludeDatabaseRoleMemberships = $true
$ScriptingOptions.ContinueScriptingOnError = $false; #ignore scripts errors, advisable to set to $false

#Server Permissions#
"USE [master]$BatchSeperator";

#Server Logins - Integrated Windows Authentication
$SQLInstance.Logins | Where-Object {@("WindowsUser","WindowsGroup") -contains $_.LoginType} |% {$_.Script($ScriptingOptions)} |% {$_.ToString()+$BatchSeperator};

#Server Logins - SQL Authentication
$SQLAuthLoginsCommand =
"
SELECT
'CREATE LOGIN ' + QUOTENAME(sp.[name]) + ' WITH PASSWORD = ' + master.dbo.[fn_varbintohexstr](CAST(LOGINPROPERTY(sp.[name], 'passwordhash') AS VARBINARY(256))) + ' HASHED'
+',SID = ' + master.dbo.[fn_varbintohexstr](sl.[sid])
+',DEFAULT_DATABASE = ' + QUOTENAME(sl.[default_database_name])
+',DEFAULT_LANGUAGE = ' + QUOTENAME(sl.[default_language_name])
 +',CHECK_EXPIRATION = ' + CASE WHEN sl.[is_expiration_checked] = 1 THEN 'ON' ELSE 'OFF' END
 +',CHECK_POLICY = ' + CASE WHEN sl.[is_policy_checked] = 1 THEN 'ON' ELSE 'OFF' END
 +';'
FROM sys.sql_logins AS sl
INNER JOIN sys.server_principals AS sp ON sl.[principal_id] = sp.[principal_id]
WHERE sp.[name] <> 'sa' AND sp.[name] NOT LIKE '##%'
"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("server=$SQLInstanceName;Integrated Security=sspi;");
$SqlCommand = New-Object System.Data.SqlClient.SqlCommand;
$SqlCommand.Connection = $SqlConnection;
$SqlCommand.CommandText = $SQLAuthLoginsCommand;

$SqlConnection.Open();
$Reader = $SqlCommand.ExecuteReader();
While ($Reader.Read()){$Reader[0]+$BatchSeperator;}
$SqlConnection.Close();

#Server Roles
foreach ($Role in $SQLInstance.Roles){$Role.EnumServerRoleMembers() | Where-Object {$_ -ne "sa"} |% {"EXEC master..sp_addsrvrolemember @loginame = N'{0}', @rolename = N'{1}'{2}" -f ($_,$Role.Name,$BatchSeperator);}};

#Server Permissions
$SQLInstance.EnumServerPermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee -and $_.Grantee -notlike "##*"} |% {
if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
"{0} {1} TO [{2}] {3}{4}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$_.Grantee,$wg,$BatchSeperator);
};

#Server Object Permissions
$SQLInstance.EnumObjectPermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee} |% {
if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
"{0} {1} ON {2}::[{3}] TO [{4}] {5}{6}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$_.ObjectClass.ToString().ToUpper(),$_.ObjectName,$_.Grantee,$wg,$BatchSeperator);
};

#Database Permissions#
$SQLInstance.Databases | Where-Object {$DatabaseNames -contains $_.Name -or $DatabaseNames -eq $null} |% {

	$ScriptingOptions.IncludeDatabaseContext = $false;"USE ["+$_.Name+"]$BatchSeperator";#setting database context once.

	#Fixed Roles
	$_.Roles | Where {$_.IsFixedRole -eq $false} |% {$_.Script($ScriptingOptions)} | Sort-Object $_.ToString() |% {"{0}{1}" -f ($_.ToString(),$BatchSeperator)};#Dependency Issue. Create Role, before add to role. Solved by sort for now.

	#Database Create User(s) and add to Role(s)
	$_.Users | Where-Object {$_.IsSystemObject -eq $false -and $_.Name -notlike "##*"} |% {
	$_.Script($ScriptingOptions)} |% {
	if ($_.Contains("sp_addrolemember")){$me = "EXEC "} else {$me = ""};
	"{0}{1}{2}" -f ($me,$_,$BatchSeperator);
	};

	#Database Permissions
	$_.EnumDatabasePermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee -and $_.Grantee -notlike "##*"} |% {
	if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
	"{0} {1} TO [{2}] {3}{4}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$_.Grantee,$wg,$BatchSeperator);
	};

	#Database Object Permissions
	$_.EnumObjectPermissions() | Where-Object {@("sa","dbo","information_schema","sys") -notcontains $_.Grantee -and $_.Grantee -notlike "##*"} |% {
	if ($_.ObjectClass -eq "Schema"){$obj = "SCHEMA::["+$_.ObjectName+"]"}
	elseif ($_.ObjectClass -eq "User"){$obj = "USER::["+$_.ObjectName+"]"}
	else {$obj = "["+$_.ObjectSchema+"].["+$_.ObjectName+"]"};
	if ($_.PermissionState -eq "GrantWithGrant"){$wg = "WITH GRANT OPTION"} else {$wg = ""};
	"{0} {1} ON {2} TO [{3}] {4}{5}" -f ($_.PermissionState.ToString().Replace("WithGrant","").ToUpper(),$_.PermissionType,$obj,$_.Grantee,$wg,$BatchSeperator);
	};
};

Setting up a Job

1. Expand SQL Server Agent
2. Right Click Jobs, New Job, Type in a Name for the Job e.g. SecurityScripter
3. Change to Steps, and Add a New step which has a type “Powershell” then copy and paste the powershell code from above.
4. On the Advanced Tab Input a file directory and file name for Powershell: Outputfile e.g C:\MyServerSecurityScripter.sql or more appropriately a secured network share.

Finally run the job to make sure it works, and if all has been successful then there should be a fully scripted output.

List Installed Software aka Get-InstalledSoftware

I needed a script to check for installed software on local and remote machines that did not rely on the Win32_Product WMI class as this will only return software that the windows installer knows about.

One way to do this is via the visual basic script List All Installed Software (Microsoft) on script center, but as I prefer Powershell I had to rewrite my own version.

Also since posting this on technet I have had some feedback that it was not working on everyones machine so please feel free to add questions or feedback here and we can iron out any bugs there maybe.

Also if anyone has any design ideas, as I am not particularly happy about the way the 64 bit version works.

Usage Examples:
Get-InstalledSoftware
Get-InstalledSoftware | Select Name,InstallDate | Format-Table
Get-InstalledSoftware | Sort-Object @{Expression={$_.ComputerName};Ascending=$True},@{Expression={$_.Name};Ascending=$True} | Format-Table
Get-InstalledSoftware "ComputerA","ComputerB" | Export-CSV -NoTypeInformation "C:\InstalledSoftware.csv";

32 Bit

#http://jongurgul.com/blog/installedsoftware/
Function Get-InstalledSoftware{
	Param([String[]]$Computers)
	If (!$Computers) {$Computers = $ENV:ComputerName}
	$Base = New-Object PSObject;
	$Base | Add-Member Noteproperty ComputerName -Value $Null;
	$Base | Add-Member Noteproperty Name -Value $Null;
	$Base | Add-Member Noteproperty Publisher -Value $Null;
	$Base | Add-Member Noteproperty InstallDate -Value $Null;
	$Base | Add-Member Noteproperty EstimatedSize -Value $Null;
	$Base | Add-Member Noteproperty Version -Value $Null;
	$Results =  New-Object System.Collections.Generic.List[System.Object];

	ForEach ($ComputerName in $Computers){
		$Registry = $Null;
		Try{$Registry = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine,$ComputerName);}
		Catch{Write-Host -ForegroundColor Red "$($_.Exception.Message)";}

		If ($Registry){
			$UninstallKeys = $Null;
			$SubKey = $Null;
			$UninstallKeys = $Registry.OpenSubKey("Software\Microsoft\Windows\CurrentVersion\Uninstall",$False);
			$UninstallKeys.GetSubKeyNames()|%{
				$SubKey = $UninstallKeys.OpenSubKey($_,$False);
				$DisplayName = $SubKey.GetValue("DisplayName");
				If ($DisplayName.Length -gt 0){
					$Entry = $Base | Select-Object *
					$Entry.ComputerName = $ComputerName;
					$Entry.Name = $DisplayName.Trim();
					$Entry.Publisher = $SubKey.GetValue("Publisher");
					[ref]$ParsedInstallDate = Get-Date
					If ([DateTime]::TryParseExact($SubKey.GetValue("InstallDate"),"yyyyMMdd",$Null,[System.Globalization.DateTimeStyles]::None,$ParsedInstallDate)){
					$Entry.InstallDate = $ParsedInstallDate.Value
					}
					$Entry.EstimatedSize = [Math]::Round($SubKey.GetValue("EstimatedSize")/1KB,1);
					$Entry.Version = $SubKey.GetValue("DisplayVersion");
					[Void]$Results.Add($Entry);
				}
			}
		}
	}
	$Results
}

64 Bit

#http://jongurgul.com/blog/installedsoftware/
Function Get-InstalledSoftware{
	Param([String[]]$Computers)
	If (!$Computers) {$Computers = $ENV:ComputerName}
	$Base = New-Object PSObject;
	$Base | Add-Member Noteproperty ComputerName -Value $Null;
	$Base | Add-Member Noteproperty Name -Value $Null;
	$Base | Add-Member Noteproperty Publisher -Value $Null;
	$Base | Add-Member Noteproperty InstallDate -Value $Null;
	$Base | Add-Member Noteproperty EstimatedSize -Value $Null;
	$Base | Add-Member Noteproperty Version -Value $Null;
	$Base | Add-Member Noteproperty Wow6432Node -Value $Null;
	$Results =  New-Object System.Collections.Generic.List[System.Object];

	ForEach ($ComputerName in $Computers){
		$Registry = $Null;
		Try{$Registry = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine,$ComputerName);}
		Catch{Write-Host -ForegroundColor Red "$($_.Exception.Message)";}

		If ($Registry){
			$UninstallKeys = $Null;
			$SubKey = $Null;
			$UninstallKeys = $Registry.OpenSubKey("Software\Microsoft\Windows\CurrentVersion\Uninstall",$False);
			$UninstallKeys.GetSubKeyNames()|%{
				$SubKey = $UninstallKeys.OpenSubKey($_,$False);
				$DisplayName = $SubKey.GetValue("DisplayName");
				If ($DisplayName.Length -gt 0){
					$Entry = $Base | Select-Object *
					$Entry.ComputerName = $ComputerName;
					$Entry.Name = $DisplayName.Trim();
					$Entry.Publisher = $SubKey.GetValue("Publisher");
					[ref]$ParsedInstallDate = Get-Date
					If ([DateTime]::TryParseExact($SubKey.GetValue("InstallDate"),"yyyyMMdd",$Null,[System.Globalization.DateTimeStyles]::None,$ParsedInstallDate)){
					$Entry.InstallDate = $ParsedInstallDate.Value
					}
					$Entry.EstimatedSize = [Math]::Round($SubKey.GetValue("EstimatedSize")/1KB,1);
					$Entry.Version = $SubKey.GetValue("DisplayVersion");
					[Void]$Results.Add($Entry);
				}
			}

				If ([IntPtr]::Size -eq 8){
                $UninstallKeysWow6432Node = $Null;
                $SubKeyWow6432Node = $Null;
                $UninstallKeysWow6432Node = $Registry.OpenSubKey("Software\Wow6432Node\Microsoft\Windows\CurrentVersion\Uninstall",$False);
                    If ($UninstallKeysWow6432Node) {
                        $UninstallKeysWow6432Node.GetSubKeyNames()|%{
                        $SubKeyWow6432Node = $UninstallKeysWow6432Node.OpenSubKey($_,$False);
                        $DisplayName = $SubKeyWow6432Node.GetValue("DisplayName");
                        If ($DisplayName.Length -gt 0){
                        	$Entry = $Base | Select-Object *
                            $Entry.ComputerName = $ComputerName;
                            $Entry.Name = $DisplayName.Trim();
                            $Entry.Publisher = $SubKeyWow6432Node.GetValue("Publisher");
                            [ref]$ParsedInstallDate = Get-Date
                            If ([DateTime]::TryParseExact($SubKeyWow6432Node.GetValue("InstallDate"),"yyyyMMdd",$Null,[System.Globalization.DateTimeStyles]::None,$ParsedInstallDate)){
                            $Entry.InstallDate = $ParsedInstallDate.Value
                            }
                            $Entry.EstimatedSize = [Math]::Round($SubKeyWow6432Node.GetValue("EstimatedSize")/1KB,1);
                            $Entry.Version = $SubKeyWow6432Node.GetValue("DisplayVersion");
                            $Entry.Wow6432Node = $True;
                            [Void]$Results.Add($Entry);
                        	}
                        }
                	}
                }
		}
	}
	$Results
}

http://gallery.technet.microsoft.com/scriptcenter/519e1d3a-6318-4e3d-b507-692e962c6666