Blog Post

Finding Where xp_cmdshell is Used

,

I saw a post recently where someone was concerned about where xp_cmdshell was in use inside their system. They felt it was a security risk, and decided to get rid of it. I don’t agree with that, and I think xp_cmdshell can be safely used, by restricting who can run it.

That being said, I was happy to help. I saw someone say search in sys.modules, but that’s not enough. This post looks at what I thought was a better solution.

When you run a query like this one, you only search in the current database.

SELECT definition

FROM sys.system_sql_modules

WHERE definition LIKE ‘%xp_cmdshell%’;

This is fine if you’re concerned here. If I run this on a sample database, I find this:

2024-07-23 14_01_26-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70))_ - Microsoft SQL Server

However, that misses a few things. First, system_sql_modules isn’t everything. In this case, I have a proc that runs xp_cmdshell that doesn’t show up. I need all_sql_modules. This has user stuff. If I run that, I see this.

2024-07-23 14_03_06-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70))_ - Microsoft SQL Server

However, that’s one database. What is better?

All databases.

To do that, we’ll use the undocumented, but useful, sp_msforeachdb. In this, I can run code as a parameter. I can do this:

EXEC sp_msforeachdb  ‘use ? SELECT definition FROM sys.all_sql_modules WHERE definition LIKE ”%xp_cmdshell%”;’

GO

The problem is I see this:

2024-07-23 14_05_14-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70)) Executing..._ - Microso

In the 4th result set, where are these things?

A better piece of code actually tells me which database is in use.

2024-07-23 14_06_05-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (70)) Executing..._ - Microso

Here’s the code I ran. Note that I use the current database parameter, the question mark, in the SELET as well as the USE.

EXEC sp_msforeachdb  ‘use ? SELECT ”?”, definition FROM sys.all_sql_modules WHERE definition LIKE ”%xp_cmdshell%”;’

GO

That gets me code inside databases, except for one place. What about jobs? I need this code:

USE msdb

GO

SELECT s2.job_id, s2.name, s.step_name FROM dbo.sysjobsteps AS s INNER JOIN dbo.sysjobs AS s2 ON s2.job_id = s.job_id

WHERE command LIKE ‘%xp_cmdshell%’

These two queries will get me the places I’ve used xp_cmdshell.

As long as I haven’t encrypted procs/functions. In that case, I need SQL Compare.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

5 (2)

You rated this post out of 5. Change rating