opc.three (1/13/2013)

---

One of the many advantages of using the SQLCLR over xp_cmdshell for interacting with the file system is that if the user connected to SQL Server under Windows Authentication you can use their credentials to access the file system instead of a proxy account, or worse, the SQL Server service account. This would prevent a user from altering anything on the file system (local or network) for which they were not expressly granted permissions too and that is something you cannot necessarily achieve with xp_cmdshell. This is not to mention the fact that when used this way the SQLCLR allows us to audit a specific user's actions meaning the action will not show up as having been carried out by the proxy account or the SQL Server service account.

I am speaking in general though, in disagreement with the above statement that implies that using the SQLCLR offers no advantages over using xp_cmdshell when interacting with the file system, which is flatly incorrect.

That said, I am not sure if the library I linked to on CodePlex uses the connected user's credentials or not, but if that were a requirement the code could be easily modified to achieve it.

Lynn Pettis (1/13/2013)

---

Of course, using SQLCLR is also dependent on whether or not you are allowed to use it in your environment as well.

EXEC sys.sp_configure 
    @configname = 'clr enabled',
    @configvalue = 1;
RECONFIGURE;

opc.three (1/13/2013)

---

Naturally. It, like sys.xp_cmdshell, is disabled by default and must be enabled using sys.sp_configure.

EXEC sys.sp_configure 
    @configname = 'clr enabled',
    @configvalue = 1;
RECONFIGURE;

Lynn Pettis (1/13/2013)

---

I know how to enable CLR. The problem with your solution comes in when you AREN"T allowed to use CLR. That is all I was saying.