Sample Header Ad - 728x90

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

3 votes
1 answer
693 views
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



$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
Asked by SE1986 (2182 rep)
Nov 25, 2021, 03:28 PM
Last activity: Dec 6, 2021, 04:53 AM