Jeff Moden (7/15/2015)
rmechaber (7/15/2015)
Jeff Moden (7/15/2015)
Everytime I see that particular post, I can't help but ask why someone would spend so much time and effort duplicating what DOS already does so well.
Really? I know you are an advocate for xp_cmdshell, Jeff, but what if that's not an option?
I agree with you that CLR is a bit of work to set up, but once it's completed you get a TVF you can query and join to. Plus, CLR can return so many more file attributes than DOS -- the archive bit (you'd need the DOS ATTRIB command to capture); full path (need xp_dirtree), etc.
I spent quite a long time a couple years ago trying to get xp_cmdshell working using information you'd posted here on SSC on a forum answer, but I never succeeded. You were even nice enough to post back a couple of times to help me troubleshoot, but all I ever got was errors.
So your comment about "so much time and effort" worked in the opposite direction for me.
I'm not a CLR evangelist, but this seems like a good use of the tool. YMMV and "it depends" are in full force!
Rich
Understood on the xp_CmdShell thing. And I do like the idea that it returns the full gambit of information and, to be honest, I wish that MS would build functionality like this into SQL Server.
I'm just curious why a company would ban the use of xp_CmdShell but allow a CLR set like this because xp_CmdShell requires SA privs (unless someone was dumb enough to grant direct execution privs to individuals, very bad thing, indeed!). If you allow lesser privs on this CLR set, then any hacker that gets in with less than SA privs can copy files as part of their payload or file insertion, freely explore your directories to find a decent payload, move files for a bit of malicious fun, or just flat out delete your files to cause you damage.
If you can guarantee that only sysadmins can use this CLR set, then it might not be such a bad thing.
Thanks Jeff. I think you might have misunderstood one aspect of CLR and security, though, or maybe I misunderstood you: CLR gets loaded into a database as an "assembly", but regular users typically aren't given any access to assemblies (you need CREATE ASSEMBLY permission).
SQL stored procedures, functions and TVFs are created from assemblies, and you grant users EXECUTE/SELECT permissions on those objects just the way you would any other proc/function/TVF.
From the user's perspective, then, it doesn't matter if the TVF gets created from T-SQL (with or without xp_cmdshell) or a CLR assembly: if the user hasn't been granted SELECT permissions on the TVF, they get nowhere.
Rich