Powershell SMO changing database context from master when setting database to SINGLE_USER

Question

I am trying to set a database to single user using SQL Server SMO and have the code below. This code is an abridged excerpt from a larger restore script

$Credential = Get-Credential

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null;
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null;

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "MY-SERVER1";
$srv.ConnectionContext.NonPooledConnection = $true;
$srv.ConnectionContext.ConnectTimeout = 0;
$srv.ConnectionContext.StatementTimeout = 0;

$srv.ConnectionContext.LoginSecure = $false;
$srv.ConnectionContext.set_Login($Credential.username);
$srv.ConnectionContext.set_SecurePassword($Credential.password);
$srv.ConnectionContext.DatabaseName = "master";

$srv.ConnectionContext.Connect()

($srv.ConnectionContext.ExecuteWithResults("SELECT DB_NAME(),@@SERVERNAME")).Tables # verify database and servername we are connected to

$srv.Databases.Item("MyDB").UserAccess = "Single";
$srv.KillAllProcesses("MyDB");

try
{
    $srv.Databases.Item("MyDB").Alter([Microsoft.SqlServer.Management.Smo.TerminationClause]::RollbackTransactionsImmediately);
}
catch
{
    Write-Host $_.Exception.GetBaseException().Message;
    Write-Host "";
}

$srv.ConnectionContext.Disconnect();

The account I pass into $credential has the following permissions in SQL Server:

GRANT CREATE ANY DATABASE TO [MyUser]
GRANT VIEW SERVER STATE TO [MyUser] 
GRANT ALTER ANY DATABASE TO [MyUser] 
GRANT ALTER ANY CONNECTION TO [MyUser]
ALTER SERVER ROLE [dbcreator] ADD MEMBER [MyUser]

When I run the Powershell code above, it fails on the $srv.Databases.Item("MyDB").Alter line with the error

The server principal "MyUser" is not able to access the database "MyDB" under the current security context.

when I open a profiler trace, I can see that it is running USE [MyDB] and MyUser doesn't exist in that database which explains the error.

if I wanted to do this in Management studio, I would run the ALTER DATABASE statement against the master database:

USE master;
ALTER DATABASE [MyDB]  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

which works when run as MyUser in SSMS.

How do I stop the SMO alter command trying to switch the database context to the MyDB my user doesn't have access to?

If possible, I am looking to amend the existing code using SMO, rather than re-write using Invoke-SqlCmd or something else as this script is used extensively in production. The script was written originally using SMO as that was the best way identified to Set a database to single user mode and Restore the database using the same SPID

Answer 1

I don't know about the PowerShell / SMO side of things, but there is something you can do that will work without requiring any modification to the PowerShell script. Just allow MyUser to connect to any database (without adding that login to any particular database) by granting the CONNECT ANY DATABASE instance/server -level permission:

GRANT CONNECT ANY DATABASE TO [MyUser];

I don't see this as a security risk given that you're already granting this login the CREATE ANY DATABASE, ALTER ANY DATABASE, and VIEW SERVER STATE permissions.

For example:

SETUP

USE [master];
CREATE LOGIN [MyUser] WITH PASSWORD=N'Pop-Pop';

GRANT CREATE ANY DATABASE TO [MyUser]
GRANT VIEW SERVER STATE TO [MyUser] 
GRANT ALTER ANY DATABASE TO [MyUser] 
GRANT ALTER ANY CONNECTION TO [MyUser]
ALTER SERVER ROLE [dbcreator] ADD MEMBER [MyUser]

TEST 1

EXECUTE AS LOGIN = N'MyUser';

USE [_SecurityTest];
-- error (as noted in the question)

REVERT;

APPLY FIX

GRANT CONNECT ANY DATABASE TO [MyUser];

TEST 2

EXECUTE AS LOGIN = N'MyUser';

USE [_SecurityTest]; -- Success!

SELECT USER_ID(), SESSION_USER;
-- 0    MyUser
-- 0 = public role

REVERT;