Cookies on ScriptPreflight

We use strictly necessary storage to sign you in, take payments and keep the site secure. With your permission we also count visits, without cookies, to see which pages help. We don't run marketing tags. Cookie policy

Blog

DELETE without a WHERE clause, delivered by PowerShell

Shaun Hardneck · · 2 min read

sql-serverpowershelldatabases
Dark ScriptPreflight cover with an unscoped DELETE statement inside Invoke-Sqlcmd highlighted.

Database clean-ups are often wrapped in PowerShell so they can be scheduled:

PowerShell
Invoke-Sqlcmd -ServerInstance SQL01 -Database Audit -Query "DELETE FROM dbo.Events"

The intent was "remove the old events". The statement removes every row in the table. There's no WHERE clause, so there's no limit.

Why this one is worse than most

  • No preview. Invoke-Sqlcmd has no -WhatIf. The statement runs the moment the line does.
  • No undo. Once committed, deleted rows are back only from a restore, and only up to your last backup.
  • It hides in strings. To PowerShell, the query is just text. A reviewer skimming the script sees a cmdlet call, not a destructive statement.

The pattern that protects you

Count first, then delete inside a transaction that checks its own count:

PowerShell
$query = @"
SET XACT_ABORT ON;
BEGIN TRANSACTION;
DELETE FROM dbo.Events WHERE EventDate < DATEADD(day, -90, SYSUTCDATETIME());
IF @@ROWCOUNT > 50000
BEGIN
    ROLLBACK TRANSACTION;
    THROW 50001, 'More rows than expected. Rolled back.', 1;
END
COMMIT TRANSACTION;
"@

Invoke-Sqlcmd -ServerInstance SQL01 -Database Audit -Query $query -ErrorAction Stop

Before that, run the same WHERE clause as a SELECT COUNT(*) and look at the number.

Three more habits:

  1. Never build the WHERE clause by pasting variables into the string. "... WHERE Id = $id" is how an empty $id or a quote in the data changes the statement. Validate values first, and prefer stored procedures with typed parameters for anything routine.
  2. Use a login that can only do the job. A clean-up account doesn't need db_owner.
  3. Know your restore point before you run it, not after.

Three-step flow: begin a transaction, delete with a WHERE clause, then roll back if the row count exceeds a limit.

What a preflight shows

For the unscoped line above, ScriptPreflight reports that it deletes rows with no WHERE clause, reaches every row in the table, and can't be undone, with a High result. It reads the SQL inside the PowerShell string; nothing is run against your database.

ScriptPreflight checks PowerShell today. Standalone .sql files get their own check as T-SQL support passes the same tests PowerShell did. Until then, wrap clean-ups in PowerShell and preflight the wrapper, or use the pattern above everywhere.

Sources