But personally, I do like having the option of setting FileMode and FileAccess.
Sure, nothing wrong with wanting more control. It might be better to change this up entirely though, and go ahead and make it fully streaming, rather than trying to read/write the whole file contents. If you're dealing with a really, really big file then ReadAllBytes/WriteAllBytes might cause problems, as would the existing code (since it's reading/writing the entire file in one big chunk).
OR, to get around any possible file system permission issues, just return the error message as the return value of the function. Meaning, instead of returning "int" (which should actually be "SqlInt32"), return SqlString and upon success return an empty string "", or else return ex.Message.
Yeah, I don't think I'd do that. Might as well just forgo the catch altogether and just let it error like any other TSQL statement would. If the code needs to clean up any resources then you can always try/finally and just let any exception bubble back up (and catch it with a TSQL TRY/CATCH).