Mistake 1: watching only sysadmin membership. CONTROL SERVER grants control over a SQL Server instance, and it can authorize xp_cmdshell use even when the principal is not sysadmin. Direct grants are easy to overlook, creating an opaque security path. section 14.4
The overlooked back door
A DBA may lack operating-system permissions but receive CONTROL SERVER to solve an operational problem. That access can become a back door from SQL Server to the operating system through xp_cmdshell. Review direct CONTROL SERVER grants, xp_cmdshell EXEC permissions, and IMPERSONATE paths—not only sysadmin membership. section 14.4
Lateral movement
Connects overlooked SQL Server permissions with lateral movement toward the operating system. section 14.4
Listing 14.5 Creating a trigger to prevent xp_cmdshell execution
EXEC sp_addmessage 50001, 16, 'Attempt to disable xp_cmdshell' ;
GO
CREATE TRIGGER prevent_xp_cmdshell
ON ALL SERVER
FOR ALTER_INSTANCE
AS
BEGIN
DECLARE @Statement NVARCHAR(4000) ;
SET @Statement = (SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(4000)')) ;
IF (CHARINDEX('sp_configure', @Statement) > 0)
AND (CHARINDEX('xp_cmdshell', @Statement) > 0)
BEGIN
RAISERROR(50001, 16, 1, 'Attempt to disable xp_cmdshell') WITH LOG ;
ROLLBACK ;
END
END ;
GO
Shows an event trigger that logs and rolls back attempts to configure xp_cmdshell. section 14.4
