Database-scoped SMO calls silently change the current database of the shared connection

[andreasjordan]

Summary

$db.Query(...), $db.Invoke(...), $db.ExecuteNonQuery(...) and $db.ExecuteWithResults(...) on an SMO Database object do not run on a private connection. SMO's Database.ExecutionManager.ConnectionContext is the parent server's connection context, so these calls issue a USE [database] on the connection the caller owns and never switch back.

Every command that uses this pattern therefore hands the connection back to the caller pointing at a different database. Anything the caller runs afterwards through $server.ConnectionContext (or through any dbatools command that reuses that open connection) executes in the wrong database.

Proof that the execution manager is not a private connection:

$server = Connect-DbaInstance -SqlInstance $instance -NonPooledConnection
$db = $server.Databases["SomeDatabase"]
[object]::ReferenceEquals($db.ExecutionManager.ConnectionContext, $server.ConnectionContext)
# True

Steps to Reproduce

$server = Connect-DbaInstance -SqlInstance $instance -NonPooledConnection
$server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()")     # master

$null = $server.Databases["SomeDatabase"].ExecuteNonQuery("SELECT 1")
$server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()")     # SomeDatabase  <-- leaked

$null = $server.Databases["SomeDatabase"].Query("SELECT 1")
$server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()")     # SomeDatabase  <-- leaked

Verified on SQL Server 2019 and 2022, dbatools 2.8.4 (PowerShell Gallery, Windows PowerShell 5.1) and the current development build (PowerShell 7.6), with Windows authentication and with a -SqlCredential holding a Windows account, on pooled and non-pooled connections. $db.Refresh() beforehand makes no difference.

$server.Query($sql, $database) is affected as well: the two-argument overload in xml/dbatools.Types.ps1xml routes through $this.Databases[$Database].ExecuteWithResults($Query), so it leaks exactly like the Database methods.

Why this is only sometimes visible

Two things mask the leak, which is why it has survived this long:

1. Pooled connections that are currently closed. The leak lives on the ServerConnection; when the connection object is closed between calls, the next ExecuteScalar reconnects at the default database and the leak is gone. Non-pooled connections (Connect-DbaInstance -NonPooledConnection) and pooled connections that are still open keep it.
2. Invoke-DbaQuery closing connections it does not own (Commands disconnect SQL Server connections they do not own #10554). Any dbatools command that ends by calling Invoke-DbaQuery -SqlInstance $server -Database <x> can close the caller's non-pooled connection, and the transparent reconnect resets the current database - wiping the leak by accident.

The second point makes the leak look unreproducible, because whether it survives depends on which command runs next. This provisioning script is what surfaced it: one shared connection, several commands against the same user database.

$server = Connect-DbaInstance -SqlInstance $sqlInstance -SqlCredential $Credential -NonPooledConnection

$null = Mount-DbaDatabase -SqlInstance $server -Database StackOverflow -FileStructure $fileStructure -DatabaseOwner sa
$null = Invoke-DbaDbUpgrade -SqlInstance $server -Database StackOverflow -NoCheckDb
$null = Set-DbaDbRecoveryModel -SqlInstance $server -Database StackOverflow -RecoveryModel Full
$null = Invoke-DbaQuery -SqlInstance $server -Database master -Query "ALTER DATABASE [StackOverflow] SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE"
$null = Set-DbaDbQueryStoreOption -SqlInstance $server -Database StackOverflow -State ReadWrite

The attached database comes in at compatibility level 100, so Invoke-DbaDbUpgrade actually does work and leaks. Set-DbaDbRecoveryModel then repairs the connection - not on purpose, it just happens to reach Invoke-DbaQuery with the database the connection is currently stuck on. Reproducing the same sequence against a test database, checking $server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()") after every step:

ok      baseline (compatibility level 100)                   DB_NAME()=master
LEAKED  Invoke-DbaDbUpgrade -NoCheckDb                       DB_NAME()=dbatoolsci_qscontext
ok      Set-DbaDbRecoveryModel                               DB_NAME()=master
ok      Invoke-DbaQuery -Database master (RCSI)              DB_NAME()=master
ok      Set-DbaDbQueryStoreOption -State ReadWrite           DB_NAME()=master

Reorder those lines, drop one of them, or run the same script against a database that does not need upgrading, and the leak moves somewhere else or disappears entirely.

A caller who tries to observe it with Invoke-DbaQuery -SqlInstance $server -Database master -Query "SELECT DB_NAME()" will never see it either: because master differs from the leaked current database, dbatools builds a separate connection and always reports master. The reliable check is $server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()").

Scope

Rough inventory over public/ and private/ (excluding tests):

• ~97 database-scoped call sites ($db*.Query/Invoke/ExecuteNonQuery/ExecuteWithResults, $server.Databases[...].*) in ~52 files
• ~29 additional $server.Query($sql, $database) style call sites
• the heaviest users: Invoke-DbaDbDataMasking (12), Copy-DbaSystemDbUserObject (5), Set-DbaTempDbConfig (4), Invoke-DbaDbUpgrade (4), Copy-DbaDbAssembly (4)

Two concrete instances are filed separately:

• Invoke-DbaDbUpgrade - Leaves the connection pointing at the upgraded database #10556 - Invoke-DbaDbUpgrade leaves the connection in the upgraded database
• Set-DbaDbQueryStoreOption - Database name passed into the wrong parameter, and the connection is left in that database #10557 - Set-DbaDbQueryStoreOption leaves the connection in the configured database (and passes the database name into the wrong parameter)

Possible fixes

Worth deciding centrally before touching individual commands:

1. Fix the dbatools wrappers. Database.Query and Database.Invoke are our own ScriptMethods in xml/dbatools.Types.ps1xml. They could run on a copied connection context ($this.Parent.ConnectionContext.Copy().GetDatabaseConnection($this.Name)) or restore the previous current database afterwards. That fixes every call site that goes through them at once, including Server.Query($sql, $db).
2. Replace direct SMO calls per call site. $db.ExecuteNonQuery(...) / $db.ExecuteWithResults(...) are SMO's own methods and cannot be shadowed, so those call sites have to be rewritten - either to the fixed wrapper, or to a server-level query when the statement does not need the database context at all (ALTER DATABASE ... is the common case).
3. Do nothing and document it. Not attractive: the connection is the caller's, and a command that silently repoints it is surprising in exactly the scripting scenarios dbatools is used for.

Option 1 plus targeted option 2 work looks like the sensible split.

How to verify a fix

A regression test per fixed command in the shape of:

$server = Connect-DbaInstance -SqlInstance $instance -NonPooledConnection
$null = <command under test> -SqlInstance $server -Database $dbName ...
$server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()") | Should -Be "master"

-NonPooledConnection matters - with a pooled connection the assertion passes for the wrong reason.

---

This text was created by Claude and reviewed by Andreas Jordan.

[andreasjordan]

A second source of the same leak: SMO scripting server-level objects

The issue documents the dbatools calls ($db.Query(), $db.ExecuteNonQuery(), $server.Query($sql, $db)). SMO does the same thing on its own when it creates or drops server-level objects, and that path cannot be fixed by changing our own script methods.

Measured against SQL Server 2025, always resetting to the user database before each step and reading DB_NAME() on the connection afterwards:

Get-DbaLogin                               keeps the context
New-DbaLogin (SMO Login.Create)            MOVES to master
Remove-DbaLogin (SMO Login.Drop)           MOVES to master
New-DbaDatabase (SMO Database.Create)      keeps the context
Remove-DbaDatabase (SMO Drop)              MOVES to master
Set-DbaSpConfigure (SMO Alter)             keeps the context
Get-DbaDatabase                            keeps the context
New-DbaDbUser (database level SMO Create)  keeps the context

So it is not "SMO writes always move the context" - reads do not, database-scoped creates do not, and a server-level Alter does not, but Create and Drop of a server-level object do. That fits the picture: those are scripted with a USE [master] in front, and nothing puts the connection back.

Why it matters for the scope of this issue

This is what actually broke Install-DbaFirstResponderKit while working on #10554, see #10564. The command opens one connection with -Database $Database, runs Get-DbaLogin | Remove-DbaLogin before signing the procedures, and every query afterwards silently ran in master:

Certificate signing failed ... | Cannot alter the object 'sp_BlitzFirst', because it does not exist or you do not have permission.

Two consequences for the options listed above:

• Option 1 does not cover this. Fixing Database.Query and Database.Invoke in xml/dbatools.Types.ps1xml leaves every New-Dba* and Remove-Dba* that creates or drops a login, a credential, an endpoint, a job or any other server-level object exactly as it is.
• The call site inventory understates the problem. The ~97 database-scoped calls are the ones we can grep for. The SMO ones are invisible in our source, because the USE is generated inside Create() and Drop().

That does not argue against fixing our own call sites - it argues that no command may assume the database context of a connection survives a call into dbatools or SMO. The reuse check that #10564 fixes in Invoke-DbaQuery is the other half of that: a command that asks for -Database X now gets X, whatever the connection did in the meantime.

---

This text was created by Claude and reviewed by Andreas Jordan.

[andreasjordan]

Inventory and a recommendation

Measured on SQL Server 2019 in the lab, always with Connect-DbaInstance -NonPooledConnection and $server.ConnectionContext.ExecuteScalar("SELECT DB_NAME()") as the probe. The rough numbers in the issue body hold up; what follows splits them into buckets that need different fixes, and corrects two things.

Correction: fixing Database.Query does not fix Server.Query($sql, $db)

Option 1 says fixing the Database script methods fixes "every call site that goes through them at once, including Server.Query($sql, $db)". It does not. Server.Query and Server.Invoke in xml/dbatools.Types.ps1xml reach past the Database wrapper straight to SMO:

$dataSet = $this.Databases[$Database].ExecuteWithResults($Query)   # Server.Query
$this.Databases[$Database].ExecuteNonQuery($Command)               # Server.Invoke

So there are four script methods to fix, not two. Still one file, but the Server pair does not inherit the fix. Confirmed by measurement:

call                                       result    DB_NAME() afterwards
$db.Query()                                LEAKED    dbatoolsci_wrap
$db.Invoke()                               LEAKED    dbatoolsci_wrap
$server.Query(sql) - one argument          ok        master
$server.Query(sql, db) - two arguments     LEAKED    dbatoolsci_wrap
$server.Invoke(sql, db) - two arguments    LEAKED    dbatoolsci_wrap
$db.ExecuteWithResults() - direct SMO      LEAKED    dbatoolsci_wrap
$db.ExecuteNonQuery() - direct SMO         LEAKED    dbatoolsci_wrap

The three buckets

Counted by grouping the receiver of every call, grep -rhoP '[\$\w\.\[\]]+\.(Query|Invoke|ExecuteNonQuery|ExecuteWithResults)\(' public/ private/ and sorting. Approximate, but the split is what matters.

Bucket	Sites	Fix
A - our four script methods, called with a database ($db.Query, $server.Databases[x].Invoke, $server.Query($sql, $db))	~68 database receivers plus 45 two-argument server calls across 28 files	one edit per method, four in total, in dbatools.Types.ps1xml
B - direct SMO ExecuteNonQuery / ExecuteWithResults on a Database object	35	per call site; cannot be shadowed
C - SMO's own Create() / Drop()	not greppable, see below	per command

For bucket B, the receivers are $db (14), $server.Databases[..] (10 plus 3 lowercase and 2 more spellings), $sourceServer.Databases[..] (3), $tempdb (2), $destDb (2), $destServer.Databases[..] (1), $smoserver.Databases[..] (1). The other 27 ExecuteNonQuery sites are on ConnectionContext or ServerConnection and are server-level, so they do not leak.

The mechanism for buckets A and B: restore, do not copy

Both candidates work. They are not equivalent:

=== option 1a: run on a copied connection context ===
    before : DB_NAME()=master  SPID=62
    query ran in          : dbatoolsci_ctx  on SPID 64
    after copied query    : DB_NAME()=master  SPID=62
    caller temp table     : still there
    copy sees temp table  : no (separate session)

=== option 1b: remember the current database and put it back ===
    before : DB_NAME()=master  SPID=62
    query ran in          : dbatoolsci_ctx  on SPID 62
    after restore         : DB_NAME()=master  SPID=62
    caller temp table     : still there

ConnectionContext.Copy().GetDatabaseConnection($name) runs on a different SPID. The caller's context is safe, but the query no longer shares the session: temp tables, SET options and session context are invisible to it, and every call opens another connection. That is a behaviour change for any command that builds session state and then queries through the wrapper, and it would be silent.

Remembering ConnectionContext.CurrentDatabase and issuing USE [...] afterwards keeps the same session and costs one round trip, and only when the context actually moved. Worth noting for that comparison: CurrentDatabase reports master even when DatabaseName is empty, so it is a reliable source - unlike the DatabaseName that Invoke-DbaQuery compares against, which is the mismatch found in #10554.

Recommendation: restore, in a finally so a failing query still puts the context back.

Bucket C is not derivable from a rule

The comment above reports "Create and Drop of a server-level object move the context to master". Measured across more object types, that rule does not hold:

Command	Context afterwards	Leaked
New-DbaLogin / Remove-DbaLogin	master	yes
New-DbaCredential / Remove-DbaCredential	master	yes
New-DbaServerRole / Remove-DbaServerRole	master	yes
New-DbaLinkedServer / Remove-DbaLinkedServer	master	yes
Remove-DbaDatabase	master	yes
New-DbaEndpoint	master	yes
Remove-DbaEndpoint	unchanged	no
New-DbaAgentJob / Remove-DbaAgentJob	msdb	yes
Set-DbaAgentJob	msdb	yes
New-DbaDatabase	unchanged	no
Set-DbaLogin, Set-DbaSpConfigure	unchanged	no
New-DbaDbUser, Remove-DbaDbUser, New-DbaDbRole, Remove-DbaDbRole	unchanged	no
Get-DbaLogin, Get-DbaDatabase	unchanged	no

Three things break the rule:

• Agent objects move to msdb, not master. Any fix that restores "to master" would be wrong here. Restoring what the caller had is the only correct rule.
• Set-DbaAgentJob leaks, so it is not only Create and Drop.
• New-DbaEndpoint leaks but Remove-DbaEndpoint does not, so it is not even uniform inside one object type.

Which means bucket C cannot be inventoried by reading code. It has to be measured per command, and the measurement above is the start of that list rather than the whole of it.

Suggested split

1. The mechanism, on its own: the four script methods in dbatools.Types.ps1xml restore the previous database in a finally, plus regression tests. That is bucket A, ~113 call sites, in one pull request, and it is the one that pays for itself.
2. Bucket B, in batches by command, rewriting $db.ExecuteNonQuery(...) either to the fixed wrapper or to a server-level query where the statement never needed the database context (ALTER DATABASE ... is the common case).
3. Bucket C, per command, restoring the caller's database after the SMO call - starting from the measured list, and extending it as commands are touched. Invoke-DbaDbUpgrade - Leaves the connection pointing at the upgraded database #10556 and Set-DbaDbQueryStoreOption - Database name passed into the wrong parameter, and the connection is left in that database #10557 are already two of these.

Step 1 is worth doing first on its own: it is a single file, it is testable in isolation, and until it is in place any per-command fix in buckets B and C can be undone by the next wrapper call in the same command.

---

This text was created by Claude and reviewed by Andreas Jordan.