This script checks whether xp_cmdshell, CLR integration (including CLR strict security), and Database Mail are enabled on the instance.
These features expand SQL Server’s capabilities beyond pure database operations. While commonly used, they increase surface area and should be intentionally configured.
This script is useful during security reviews, environment validation, or when inheriting a server.
The Script
What These Settings Mean
xp_cmdshell
Allows SQL Server to execute operating system commands. This can be useful for automation, but it enables command-line access from within SQL Server and should be controlled carefully.
CLR enabled
Allows .NET assemblies to run inside SQL Server. Depending on permission level, assemblies may interact with external resources or system components.
CLR strict security
When enabled, forces assemblies to be treated as UNSAFE unless explicitly signed. This provides stronger protection against untrusted CLR code.
Database Mail XPs
Enables SQL Server to send email through configured mail profiles. Commonly used for job alerts and monitoring notifications.
Example Output
The result set shows the configured value and value currently in use for each feature. A value of 1 means enabled, and 0 means disabled.
This provides a quick view of instance-level surface area configuration.
Notes
- These are instance-level configuration settings
- Changes may require RECONFIGURE to take effect
- CLR strict security is recommended when CLR is enabled
- Features should be enabled intentionally, not by default
Leave a Reply