Check file existence from sql 2005 without needing SA permissions

Question

Hi,

I have been trying to get xp_fileexist in sql 2005 to run under a sql account without having to grant sysadmin permissions. The issue is that I am unable to get it to run otherwise - the value it returns is 0 despite knowing the file exists. Points to keep in mind:

1. The share that the images/files are stored in is not local to the sql instance.
2. I have tried to make this work and have been successful by creating local user accounts and granting permissions as required, but we need to have a sql login execute the extended proc.
3. If I do not use the ext. proc, what other options besides a CLR function, can I use? I can't use the xp_cmdshell, either.

Thanks,

Maria

Answer 1

Use CLR procedure from an assembly marked with EXTERNAL ACCESS.