How to grant a login permission to xp_fileexist

Question

How to grant a login permission to xp_fileexist

William Plourde

SSCrazy

Points: 2035
More actions
December 17, 2009 at 8:35 am

#217708

I have an application database that performs several large imports and exports. These operations are excecuted on demand as users use my c# winform front end.
To do this I have stored procedures that use xp_cmdshell to run ssis packages using dtexec.
So far this has worked out very good but I've run into a small snag. My application logs in with a standard security account. Lets call it 'AppUser'.
I have beefed up my stored procedure logic to do some checks for the existance of files on the server as part of these imports and exports.
When I run the SPs as myself they work fine but when they run under the security context of AppUser the xp_fileexist always returns 0.
I have verified this by running a test script with the different logins. I'm guessing that my AppUser account does not have permissions to master.xp_fileexists, but it does not raise an error or security message when called, it just returns 0 which is bit misleading.
Is there a way that I can grant execute on master.xp_fileexists to my AppUser login ?
Thanks.
Bill, Charlotte NC
btw, I understand there are security considerations but my users need to run ssis packages to export many thousands of rows to excel output files on demand and this seemed like a good way to do it, but if there are other viable alternatives I don't know what they are.

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

This doesn't seem like a permissions issue, per se, as the procedure executes without error.

I'd recommend checking to see if the file exists in SSIS. You can use a script task for that and exit out if the file doesn't exist.

You could also do a PowerShell script to check to see if the file exists and run it in the first step of the job and have it skip the second step if the file doesn't exist.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

William Plourde SSCrazy Points: 2035 More actions · Answer 2

Thanks for the suggestions. I'm not using powershell yet but it's definately on my need-to-learn list. The script task is also a good idea but I need to check for several files before executing the ssis package.

I did find an alternate solution that works well. I stumbled upon a google search result that shows how to use xm_cmdshell 'dir ...' to get the directory contents into a temp table, then I can use exists to see if the result contains the file name I'm looking for. This works consistently and runs correctly under all the logins that I need. I also encapsulated this into a utility stored procedure that I can call in a fashion very similar to xp_fileexists.

Also, to follow up, I did find a fair number of other people that had the same problem with xp_fileexist..it only works if it runs as sa. If run as a login other than sa it always returns file not found.

aguidas 69207 Valued Member Points: 68 More actions · Answer 3

Hello Bill, WOuld you please give the source of your solutions? Thanks.

jim Etheridge SSC-Addicted Points: 430 More actions · Answer 4

You can also use XP_CMDSHELL with a variable to see if the file exists, as follows:

DECLARE @Exists integer;

EXEC @Exists=XP_CMDSHELL 'DIR C:\directory\filename.ext'

If @Exists=0, then the file exists. This saves having to declare and query a temp table, but requires that you know the file name and extension.