December 5, 2013 at 3:48 pm
I need to check if file exists on a network share using SQL.
I use EXEC Master.dbo.xp_fileexist @filename
It returns 0 even if file exists on a remote disk.
In the Security area I see the login as domain_name\sqlserverdev.
When I go to a network share to the file location and grand sqlserverdev user the read access,
xp_fileexists returns 1. It can see that file exists. So it's a permission issue.
At the same time for security reasons I can't allow domain_name\sqlserverdev user to access network shares since every SQL server in the network has this user.
We have another domain user that is used to run applications. That user has access to the network shares.
Is there any way to run xp_fileexist under a different domain user, not a default one?
Maybe it's possible to use a proxy somehow?
The purpose is to use xp_fileexist but not to grant \sqlserverdev access to the network share for security reasons.
If this doesn't work, what are other ways to determine if file exists ON A NETWORK SHARE using SQL?
EXEC @output = XP_CMDSHELL 'DIR "E:\file.txt" /B', NO_OUTPUT ?
But this only checks for files on the SQL server itself and not on a network share.
Or do bulk insert and check for exeptions?
Or write some executable and kick it from the SQL somehow each time I need to do a check?
What are my options?
December 5, 2013 at 6:17 pm
What if you wrap the call inside a stored procedure or function and use EXECUTE AS to change the privileges the command is executed under?
December 5, 2013 at 6:27 pm
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply