Estou tentando definir um banco de dados para usuário único usando o SQL Server SMO e tenho o código abaixo. Este código é um trecho abreviado de um script de restauração maior
$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();
A conta para a qual passo $credential
tem as seguintes permissões no 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]
Quando executo o código do Powershell acima, ele falha na $srv.Databases.Item("MyDB").Alter
linha com o erro
O principal do servidor "MyUser" não pode acessar o banco de dados "MyDB" no contexto de segurança atual.
quando abro um rastreamento de criador de perfil, posso ver que ele está em execução USE [MyDB]
e MyUser
não existe nesse banco de dados, o que explica o erro.
se eu quisesse fazer isso no Management studio, eu executaria a ALTER DATABASE
instrução no banco de dados mestre:
USE master;
ALTER DATABASE [MyDB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
que funciona quando executado como MyUser
no SSMS.
Como faço para parar o comando SMO alter tentando alternar o contexto do banco de dados para o que MyDB
meu usuário não tem acesso?
Se possível, estou procurando alterar o código existente usando SMO, em vez de reescrever usando Invoke-SqlCmd
ou qualquer outra coisa, pois esse script é usado extensivamente na produção. O script foi escrito originalmente usando SMO, pois essa era a melhor maneira identificada de definir um banco de dados para o modo de usuário único e restaurar o banco de dados usando o mesmo SPID
Não sei sobre o lado do PowerShell / SMO, mas há algo que você pode fazer que funcionará sem exigir nenhuma modificação no script do PowerShell. Apenas permita
MyUser
conectar-se a qualquer banco de dados (sem adicionar esse login a nenhum banco de dados específico) concedendo aCONNECT ANY DATABASE
permissão de nível de instância/servidor:Não vejo isso como um risco de segurança, pois você já está concedendo a este login as permissões
CREATE ANY DATABASE
,ALTER ANY DATABASE
e .VIEW SERVER STATE
Por exemplo:
CONFIGURAR
TESTE 1
APLICAR CORREÇÃO
TESTE 2