LinkedIn | FaceBook | Twitter
In SQL Server, there are times that you need to do things in the operating system, and to allow that there is a feature called CmdExec. This is not always a good thing –whenever you leave the confines of SQL Server and go out to the operating system, you can cause issues, not the least of which are security-related.
This best practice is primarily aimed at SQL Server 2000 – in SQL Server 2005 and higher, you’ll have these as job step types in SQL Server Agent (or ActiveX). What you should to do is ensure that only the sysadmins role can run CmdExec job steps.
In SQL Server 2005 and higher, you should use other methods to work with the operating system, such as SQL CLR or PowerShell to handle that with better safety and security.