SQL Server Agent PowerShell job step

Some time ago I wrote about this in another blog entry, and I do it again while there are some major changes in what I want to use.
First of all I want the SQL Server Agent jobstep to be defined as a PowerShell job step, not a CmdExec job step.
Also I have some other issues I want to handle.

1. The job step is defined in a script file, not a script in the job step command field.
2. The script file takes input by named parameters.
3. There are one or more spaces in the script file path, e.g. "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\JOBS".

When I looked around on the internet, two different solutions were used.
One using the PowerShell call operator ampersand (&).
One using the Invoke-Expression CmdLet. The last should be avoided while it has some risks. These risks are described in the book "Windows PowerShell in Action, Second Edition" (ISBN-13 978-1-935182-13-9) by Bruce Payette in section 21.6.4. This book is most recommend.

Actually it turned out to be quite simple, when I had the quotation marks in place.
& "D:\Microsoft SQL Server\MSSQL10.SANDY\MSSQL\JOBS\SQLPS_test.ps1" -param_00 'Zero' -param_01 'One'
The trick is to put the full name of the script file in quotation marks, and the parameter names and values outside the quotation marks.

Whe you define the job step usin the stored procedure msdb.dbo.sp_add_jobstep, the value of the parameter @command has a lot of quotation marks.
@command=N'& "D:\Microsoft SQL Server\MSSQL10.SANDY\MSSQL\JOBS\SQLPS_test.ps1" -param_00 ''Zero'' -param_01 ''One'''
Please notice that some quotation marks are single and some are double.

The script file is like this.
param(
  [Parameter(Mandatory=$true)][string]$param_00,
  [Parameter(Mandatory=$true)][string]$param_01
)
"{0:s}  Hello." -f $([System.DateTime]::Now)
"{0:s}  Parameter value 00 = $param_00" -f $([System.DateTime]::Now)
"{0:s}  Parameter value 01 = $param_01" -f $([System.DateTime]::Now)
"{0:s}  Goodbye." -f $([System.DateTime]::Now)

and the output is like
2012-02-14T07:50:03  Hello.
2012-02-14T07:50:03  Parameter value 00 = Zero
2012-02-14T07:50:03  Parameter value 01 = One
2012-02-14T07:50:03  Goodbye.

Change database owner

I am about to join an internal course, and would like to prepare by installing the SQL Server Database Product Samples from CodePlex.
After the installation, the databases were owner by my login, and I would like to have the owner changed to "sa". I my case it is renamed, but that is another story.

Renaming a database in SQL Server Management Studio is done on the properties of the database in the page "Files".
When I ask for the change script by clicking the Script drop-down, I get a script that uses the stored procedure "sp_changedbowner".
The article on this procedure in Books Online (MSDN Library) notes that this feature will be removed, and that I should use ALTER AUTHORIZATION instead.
Then I rewrote the statement to use the recommendation
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
Please recall that I have renamed "sa".

The samples generates several databases. Six in my case.
Instead of generating a script for each database, I made a script to generate these scripts
SELECT N'ALTER AUTHORIZATION ON DATABASE::[' + [databases].[name] + N'] TO [sql_sa];' AS [DCL]
FROM [master].[sys].[databases]
WHERE [databases].[owner_sid] != (
  SELECT [server_principals].[sid]
  FROM [master].[sys].[server_principals]
  WHERE [server_principals].[name] = N'sql_sa'
);

I could have generated the scripts for the first principal, as "sa" always is the first, but maybe another day I would like to use another principal.

The generated scripts are like this
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT2008R2] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorks] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksDW] TO [sql_sa];
ALTER AUTHORIZATION ON DATABASE::[AdventureWorksLT] TO [sql_sa];

Attending TechEd North America 2012