Understanding when, in a PowerShell script, the call of a method produces output upon return from the call and when it doesn’t
A colleague asked the following a few days ago:
<MESSAGE>
With the following rows I’m trying to revoke “connect” permissions from database user.
Unfortunately I always get the output from the red line (see script below)
Output:
I ‘d like to suppress the output but sadly without success. Redirection into nothing with “$null” and “out-null” does not work.
Here is the script:
$Server="OSNOLAI\KILIMANJAROX86"
$srv=New-Object Microsoft.SqlServer.Management.Smo.Server($Server)
$Login="MondoYLirondo"
$dbs=$srv.Databases
foreach ($db in $dbs)
{
foreach ($user in $db.users)
{
if ($user.Login -eq $Login)
{
$DBPermSet = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet
$DBPermSet.Add([Microsoft.SqlServer.Management.Smo.DatabasePermission]::Connect)
$db.Revoke($DBPermSet, $user.Name)
}
}
}
</MESSAGE>
Long shot answers like the following where given, but none of them happened to work it out:
- Try this and see if it works [void] $db.Revoke($DBPermSet, $user.Name)
- Does “ $db.Revoke() ” support “-EA SilentlyContinue”?
A closer look to the script and the documentation of the classes and methods used in it reveals that it happens to be the Add method of the DatabasePermissionSet class the one which appends those results to the output. Making that one (and not anything else in the script) voidable by prefixing it with [void] as in here would prevent the PS engine to collect that output to print it out to the stdout console:
[void] $DBPermSet.Add([Microsoft.SqlServer.Management.Smo.DatabasePermission]::Connect)
$Server="OSNOLAI\KILIMANJAROX86"
$srv=New-Object Microsoft.SqlServer.Management.Smo.Server($Server)
$Login="MondoYLirondo"
$dbs=$srv.Databases
foreach ($db in $dbs)
{
foreach ($user in $db.users)
{
if ($user.Login -eq $Login)
{
$DBPermSet = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet
$DBPermSet.Add([Microsoft.SqlServer.Management.Smo.DatabasePermission]::Connect)
$db.Revoke($DBPermSet, $user.Name)
}
}
}
·0:006> k
Child-SP RetAddr Call Site
Microsoft_SqlServer_Smo_ni!Microsoft.SqlServer.Management.Smo.DatabasePermissionSet.get_AlterAnyApplicationRole(...)
System_Management_Automation_ni!DynamicClass.getter(...)+0x9
System_Management_Automation_ni!System.Management.Automation.Adapter.BasePropertyGet(...)+0x77
System_Management_Automation_ni!System.Management.Automation.PSProperty.GetAdaptedValue(...)+0x1e
System_Management_Automation_ni!Microsoft.PowerShell.Commands.Internal.Format.MshExpression.GetValue(...)+0x108
System_Management_Automation_ni!Microsoft.PowerShell.Commands.Internal.Format.MshExpression.GetValues(...)+0xfa
System_Management_Automation_ni!Microsoft.PowerShell.Commands.Internal.Format.PSObjectHelper.GetExpressionDisplayValue(...)+0x33
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.ViewGenerator.GetExpressionDisplayValue(...)+0x82
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.ListViewGenerator.GenerateListViewEntryFromProperties(...)+0x1b6
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.ListViewGenerator.GeneratePayload(...)+0x5f
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.InnerFormatShapeCommand.WritePayloadObject(...)+0x28
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.InnerFormatShapeCommand.ProcessRecord(...)+0xb9
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.FrontEndCommandBase.ProcessRecord(...)+0x3d
System_Management_Automation_ni!System.Management.Automation.CommandProcessor.ProcessRecord(...)+0x211
System_Management_Automation_ni!System.Management.Automation.CommandProcessorBase.DoExecute(...)+0x83
System_Management_Automation_ni!System.Management.Automation.Internal.PipelineProcessor.DoStepItems(...)+0x78
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.CommandWrapper.Process(...)+0x67
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.OutCommandInner.ApplyFormatting(...)+0xc8
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.OutCommandInner.ProcessRecord(...)+0x97
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.FrontEndCommandBase.ProcessRecord(...)+0x3d
System_Management_Automation_ni!System.Management.Automation.CommandProcessor.ProcessRecord(...)+0x211
System_Management_Automation_ni!System.Management.Automation.CommandProcessorBase.DoExecute(...)+0x83
System_Management_Automation_ni!System.Management.Automation.Internal.PipelineProcessor.DoStepItems(...)+0x78
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.CommandWrapper.Process(...)+0x67
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.SubPipelineManager.Process(...)+0x58
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.OutputManagerInner.ProcessRecord(...)+0xd5
Microsoft_PowerShell_Commands_Utility_ni!Microsoft.PowerShell.Commands.Internal.Format.FrontEndCommandBase.ProcessRecord(...)+0x3d
System_Management_Automation_ni!System.Management.Automation.CommandProcessor.ProcessRecord(...)+0x211
System_Management_Automation_ni!System.Management.Automation.CommandProcessorBase.DoExecute(...)+0x83
System_Management_Automation_ni!System.Management.Automation.ParseTreeNode.AppendResult(...)+0x6f <-- Because the $DBPermSet.Add(::Connect) is not voidable and it returns an instance of DatabasePermissionSet, this AppendResult method is called to basically call the getters of the public properties from the object returned to retrieve the values that will be written to the output console.
System_Management_Automation_ni!System.Management.Automation.StatementListNode.ExecuteStatement(...)+0xde <-- This is the frame that is executing the $DBPermSet.Add(::Connect)
System_Management_Automation_ni!System.Management.Automation.StatementListNode.Execute(...)+0xcf
System_Management_Automation_ni!System.Management.Automation.ifStatementNode.Execute(...)+0x178
System_Management_Automation_ni!System.Management.Automation.StatementListNode.ExecuteStatement(...)+0xde
System_Management_Automation_ni!System.Management.Automation.StatementListNode.Execute(...)+0xcf
System_Management_Automation_ni!System.Management.Automation.foreachStatementNode.Execute(...)+0x39c
System_Management_Automation_ni!System.Management.Automation.StatementListNode.ExecuteStatement(...)+0xde
System_Management_Automation_ni!System.Management.Automation.StatementListNode.Execute(...)+0xcf
System_Management_Automation_ni!System.Management.Automation.foreachStatementNode.Execute(...)+0x39c
System_Management_Automation_ni!System.Management.Automation.StatementListNode.ExecuteStatement(...)+0xde
System_Management_Automation_ni!System.Management.Automation.StatementListNode.Execute(...)+0xcf
System_Management_Automation_ni!System.Management.Automation.ParseTreeNode.Execute(...)+0x26
System_Management_Automation_ni!System.Management.Automation.ScriptCommandProcessor.ExecuteWithCatch(...)+0x237
System_Management_Automation_ni!System.Management.Automation.ScriptCommandProcessor.RunClause(...)+0x4d4
System_Management_Automation_ni!System.Management.Automation.CommandProcessorBase.DoComplete(...)+0xec
System_Management_Automation_ni!System.Management.Automation.Internal.PipelineProcessor.SynchronousExecuteEnumerate(...)+0x153
System_Management_Automation_ni!System.Management.Automation.Runspaces.LocalPipeline.InvokeHelper(...)+0x4b5
System_Management_Automation_ni!System.Management.Automation.Runspaces.LocalPipeline.InvokeThreadProc(...)+0x367
mscorlib_ni!System.Threading.ExecutionContext.Run(...)+0x9b
mscorlib_ni!System.Threading.ThreadHelper.ThreadStart(...)+0x4d
mscorwks!CallDescrWorker+0x82
mscorwks!CallDescrWorkerWithHandler+0xd3
mscorwks!MethodDesc::CallDescr+0x2b1
mscorwks!ThreadNative::KickOffThread_Worker+0x191
mscorwks!ManagedThreadBase_DispatchInner+0x2c
mscorwks!ManagedThreadBase_DispatchMiddle+0x9d
mscorwks!ManagedThreadBase_DispatchOuter+0x31
mscorwks!ManagedThreadBase_FullTransitionWithAD+0x35
mscorwks!ThreadNative::KickOffThread+0xd3
mscorwks!Thread::intermediateThreadProc+0x78
KERNEL32!BaseThreadInitThunk+0xd
ntdll!RtlUserThreadStart+0x1d
Making the statement voidable discards the object returned by the method (i.e. it doesn’t call the getter of all its public properties.) To do so, you can prefix the statement with [void] as follows:
$Server="OSNOLAI\KILIMANJAROX86"
$srv=New-Object Microsoft.SqlServer.Management.Smo.Server($Server)
$Login="MondoYLirondo"
$dbs=$srv.Databases
foreach ($db in $dbs)
{
foreach ($user in $db.users)
{
if ($user.Login -eq $Login)
{
$DBPermSet = New-Object Microsoft.SqlServer.Management.Smo.DatabasePermissionSet
[void] $DBPermSet.Add([Microsoft.SqlServer.Management.Smo.DatabasePermission]::Connect)
$db.Revoke($DBPermSet, $user.Name)
}
}
}