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:
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.
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:
In the 4th result set, where are these things?
A better piece of code actually tells me which database is in use.
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.
Voice of the DBA 