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)

    }

  }

}