我正在尝试使用 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 DATABASE
instance/server 级别的权限,即可连接到任何数据库(无需将该登录名添加到任何特定数据库):鉴于您已经授予此登录 、 和 权限,我不认为这是
CREATE ANY DATABASE
安全ALTER ANY DATABASE
风险VIEW SERVER STATE
。例如:
设置
测试 1
应用修复
测试 2