Lempster (8/23/2012)

---

I don't know if anyone is still looking at this old thread - I've got a huge backlog of SQLServerCentral emails to work through - but I just wanted to thank all the contributors for a great discussion.

And thanks to Jonathan for the original article. I have not seen a better introduction to the use of CLRs and even though I have not used one myself up until now I will certainly be more open-minded to the use of them from now on.
Kudos to Jeff too for being gracious enough to take the time to properly investigate the use of a method that he would personally avoid.
Personally I do not have xp_cmdshell enabled, but use a stored procedure to create a self-deleting SQL Server Agent job to invoke xp_cmdshell, do whatever is necessary, e.g. file system manipulation, bcp and then exit.
Latterly I've also been looking at using PowerShell to carry out O/S tasks as I'm of the opinion that such tasks should be outside of the remit of T-SQL... at least until/unless MS add some proper file manipulation functions to T-SQL as one of the previous posts mentioned...can't remember which, sorry.

Anyway, a great debate that perfectly demonstrates the power of these forums and that we can all learn from each no other no matter how set in our ways we may be.

Regards
Lempster

Jeff Moden (8/23/2012)

---

As a bit of a sidebar, I use XP_CmdShell to run PowerShell. It's quite safe to do if the system is properly locked down (only DBAs have SA privs and no one has proxy rights to run xp_CmdShell directly... only via stored procedures they've been granted privs to).

rmechaber (8/24/2012)

---

Jeff, would you be willing to elaborate on this, maybe with a link or two? Specifically, I'm interested in how you control proxy rights to xp_CmdShell (to prevent ad hoc use) and if there's anything in particular needed when building stored procedures to execute it?

Thanks very much,
Rich

Jeff Moden (8/24/2012)

---

I have a demo script at home that I could share. I'll post it tonight.

rmechaber (10/3/2012)

---

Hi, Jeff, any luck running down that demo? I'd very much appreciate it!
Rich

robert.baker 21596 (10/3/2012)

---

I resolved my error:
The process does not possess the 'SeSecurityPrivilege' privilege which is required for this operation.
when I re-read the BOL around CLR Security integration, and realised it was impersonating the SQL Service account for os operations like file copy. I just had to adjust the permissions of the service account so it matched the service account on the old 2005 server.
Sorry for polluting the conversation with newbie stuff