將數據庫設置為 SINGLE_USER 時，Powershell SMO 從 master 更改數據庫上下文

我正在嘗試使用 SQL Server SMO 將數據庫設置為單個使用者，並具有以下程式碼。此程式碼是較大還原腳本的節選

$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();

我傳入的帳戶$credential在 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]

當我執行上面的 Powershell 程式碼時，它失敗並$srv.Databases.Item("MyDB").Alter出現錯誤

伺服器主體“MyUser”無法在目前安全上下文下訪問數據庫“MyDB”。

當我打開探查器跟踪時，我可以看到它正在執行USE [MyDB]並且MyUser在解釋錯誤的數據庫中不存在。

如果我想在 Management Studio 中執行此操作，我將對ALTER DATABASE主數據庫執行該語句：

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

MyUser在 SSMS 中執行時有效。

如何停止嘗試將數據庫上下文切換到MyDB我的使用者無權訪問的 SMO alter 命令？

如果可能的話，我希望使用 SMO 修改現有程式碼，而不是重寫 usingInvoke-SqlCmd或其他東西，因為該腳本在生產中廣泛使用。該腳本最初是使用 SMO 編寫的，因為這是將數據庫設置為單使用者模式並使用相同的 SPID 還原數據庫的最佳方法

我不了解 PowerShell / SMO 方面的事情，但是您可以做一些事情，而無需對 PowerShell 腳本進行任何修改。只需MyUser通過授予CONNECT ANY DATABASEinstance/server 級別的權限，即可連接到任何數據庫（無需將該登錄名添加到任何特定數據庫）：

GRANT CONNECT ANY DATABASE TO [MyUser];

鑑於您已經授予此登錄 、 和 權限，我不認為這是CREATE ANY DATABASE安全ALTER ANY DATABASE風險VIEW SERVER STATE。

例如：

設置

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]

測試 1

EXECUTE AS LOGIN = N'MyUser';

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

REVERT;

應用修復

GRANT CONNECT ANY DATABASE TO [MyUser];

測試 2

EXECUTE AS LOGIN = N'MyUser';

USE [_SecurityTest]; -- Success!

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

REVERT;

引用自：https://dba.stackexchange.com/questions/303116