"SHOWPLAN permission denied" in Database Engine Tuning Advisor

I recently ran into an issue while trying to tune a database used by an ASP.NET website. The error displayed in the Database Engine Tuning Advisor was:

[Microsoft][SQL Server Native Client 10.0][SQL Server]SHOWPLAN permission denied in database 'database'.

After a lot of digging, I managed to track down an obscure post that mentioned DTA runs statements as the user who originally executed the statement - in this case the site's application pool user. This user is locked down and does not have SHOWPLAN permission, even though I, as administrator do have permission.

The solution in the development database (where security is not a major concern) was to grant SHOWPLAN permission to the application pool user:

grant showplan to [domain\user]

Generating random date data in SQL Server 2005

The statement below will generate random dates between 1 January 2010 and 27 Apr 2011.

select cast(cast('1 Jan 2010' as datetime) as int); --returns 40177
select cast(cast('27 Apr 2011' as datetime) as int) - cast(cast('1 Jan 2010' as datetime) as int); --returns 481

update transactions
set requesteddatetimeutc = cast((RAND(convert(varbinary,newid()))*481 + 40177) as datetime);

Setting the current culture of a website

Rather than relying on the settings of the server, it can be prudent to set the current culture of your ASP.NET application using the following setting:

  ...
  <system.web>
    ...
    <globalization culture="en-NZ" uiCulture="en-NZ" />
    ...
  </system.web>

Misleading error message in PowerShell script: "Invalid assignment expression. The left hand side of an assignment operator needs to be something that can be assigned to like a variable or a property"

I recently ran into the following error message while modifying a PowerShell script:

Invalid assignment expression. The left hand side of an assignment operator needs to be something that can be assigned to like a variable or a property

The cause of the error was that I had added a line of code in front of my param declaration:

Set-StrictMode -Version 2

param(
    [string][parameter(mandatory=$true )] $WebsitePhysicalPath,

Serialise value to XML in one line of C#

This one-liner is useful in the immediate window of Visual Studio to serialise an object to XML:

new XmlSerializer(OBJECT.GetType()).Serialize(new System.IO.StreamWriter(@"c:\temp\x.xml"), OBJECT);

Note that this is not production ready because it does not dispose of objects that should be disposed!

Event log permissions in Server 2008

Configuring the permissions for an event log in Server 2003 and earlier required processing the SDDL at this registry entry:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Eventlog\Application\CustomSD

To get the SDDL in Server 2008, use the following PowerShell:

$currentSddl = ([xml](wevtutil.exe gl $logName /f:xml)).channel.channelAccess

And to write it back:

wevtutil sl $logName /ca:$newSddl