Write all the records of a table to a text file on the local disk with in T-SQL (SP)

Question

Write all the records of a table to a text file on the local disk with in T-SQL (SP)

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

Jeff Moden (5/4/2012)
opc.three (5/3/2012)
- Your case first...If executed by someone in the instance via a Windows Login then the SQLCLR object can access the file system in the context of the associated Windows account if the developer implemented implicit* impersonation within the object (a trivial task), meaning if Windows Login DOMAIN\PersonName executed my SQLCLR function it would access the file system as DOMAIN\PersonName. If not using impersonation it will access the file system using the SQL Server service account (the default). Using the executing Windows account via impersonation while granting that Windows account least privilege to do necessary work in the file system would be my first preference.
Man, thanks for the extra work you went through for this answer. I appreciate it, Orlando.

Not at all, Jeff. After all the times you have helped me along, or just plain fixed my wagon, I am more than happy to return the favor.

Correct me if I'm wrong, please, but it sounds like any of the above would allow a pissed off user to delete files that either (s)he had access to or the system had access to...

Quite possibly. It would depend on how the SQLCLR object were developed. If it were me setting things up I would default to implementing impersonation under the EXTERNAL_ACCESS security level, meaning the caller's security context would be used to access the file system, not the Service Account.

To me this is where the SQLCLR sets itself apart from xp_cmdshell. With xp_cmdshell we have two security context choices, the Service Account (sysadmins) or the proxy account (non sysadmins).

With SQLCLR I can use the caller's context all the way through the stack and rely on least privilege in the file system and on the network, on a per-user basis. In most cases this behavior will allow me to avoid using wrapper code, although as Lowell showed that is an additional option in cases where we want or need a customized layer in between the caller and the file system.

I have not attempted it, but plan to try EXECUTE AS to see how that might affect a SQLCLR object with EXTERNAL_ACCES level using impersonation, whether wrapper code using EXECUTE AS could give us in effect the ability to proxy our calls into an alternate security context when accessing the file system, similar to having n xp_cmdshell proxy accounts available at any given time.

...someone tell me how that's safer than running xp_CmdShell to do the same thing (delete files).
The real key here is that users should not be allowed to do things like delete files. ONLY the system should be allowed to do that. Users should ONLY have PUBLIC privs and the ability to execute certain stored procedures. Certainly, they should never be given privs to be able to name the file they want to delete whether it be through xp_CmdShell or some SQLCLR utility.

I guess I do not make a distinction between a person being able to use Windows Explorer to visit a UNC path to delete a file, and them using a SQLCLR object. In both cases, if I setup my objects as described above, I can know it is them deleting that file. If I was not OK with them deleting the file, they would not have the delete permission. Regardless of the vehicle I can audit it, or block it if I need to.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Joe-420121 Hall of Fame Points: 3152 More actions · Answer 2

thanks for all the contributions you all provided. I learnt a lot of new information. thanks again.