# go to the server level
PS SQLSERVER:\SQL\XU-ACER-PC>
dir
<#
Instance Name
-------------
DEFAULT
I2
#>
# Limit to the default instance
PS SQLSERVER:\SQL\XU-ACER-PC>
$a=dir
| ? {$_.InstanceName
-eq ""}
PS SQLSERVER:\SQL\XU-ACER-PC>
$a
<#
Instance Name
-------------
DEFAULT
#>
# Find all of members at the server level
PS SQLSERVER:\SQL\XU-ACER-PC>
$a | gm
# We are interested in "configuration Property
Microsoft.SqlServer.Management.Smo.Configuration Configuration
{get;}"
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration
# We are further interested in the next level of properties
such as ShowAdvancedOptions or XPCmdShellEnabled
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration
| GM
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.properties |
ft DisplayName, ConfigValue,RunValue, IsDynamic
-auto
# We can change these properties
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.ShowAdvancedOptions.ConfigValue=1
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.Alter()
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.ShowAdvancedOptions |
ft DisplayName, ConfigValue,RunValue, IsDynamic
-auto
<#
DisplayName
ConfigValue RunValue IsDynamic
-----------
----------- -------- ---------
show advanced options
1 0 True
#>
# But we will get an error for the one below
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.ShowAdvancedOptions.xp_cmdshell=1
<#
The property 'xp_cmdshell' cannot be found on this object.
Verify that the property exists and can be set.
At line:1 char:1
+ $a.configuration.ShowAdvancedOptions.xp_cmdshell=1
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [],
RuntimeException
+
FullyQualifiedErrorId : PropertyAssignmentException
#>
# The error above is because xp_cmdshell is the display name,
not the name. We need to find the name for the property first. It is called
XPCmdShellEnabled.
# In the ShowAdvancedOption example, the name and display name
happen to the same.
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.XPCmdShellEnabled.ConfigValue=1
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.Alter()
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.xpcmdshellEnabled |
ft DisplayName, ConfigValue,RunValue, IsDynamic
-auto
<#
DisplayName ConfigValue RunValue IsDynamic
----------- ----------- -------- ---------
xp_cmdshell
1 1 True
#>
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.XPCmdShellEnabled.ConfigValue=0
PS SQLSERVER:\SQL\XU-ACER-PC>
$a.configuration.xpcmdshellEnabled |
ft DisplayName, ConfigValue,RunValue, IsDynamic
-auto
<#
DisplayName ConfigValue
RunValue IsDynamic
----------- ----------- --------
---------
xp_cmdshell 0 1 True
#>
#>
