question

MoloneyDoug-0275 avatar image
0 Votes"
MoloneyDoug-0275 asked ErlandSommarskog answered

Extended stored procedure xp_fileexist appears to require sysadmin permissions

xp_fileexist appears to require sysadmin permissions.


If I run this with sysadmin

exec xp_fileexist '\\servername\appfolder\filename.txt'

I get File Exist with a 1

However, when sysadmin permissions are removed it returns File Exists with a 0.

Does anyone know of any other way to grant the permission to return the correct result.

sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HafeezUddin-8965 avatar image
1 Vote"
HafeezUddin-8965 answered molonede commented

You can try

SELECT * FROM sys.dm_os_file_exists('\\servername\appfolder\filename.txt');

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks, that does work, but was looking to figure out permissions for xp_fileexists that does not require sysadmin role.

0 Votes 0 ·
TomPhillips-1744 avatar image
2 Votes"
TomPhillips-1744 answered

I highly recommend against using undocumented extended procs or TSQL for file manipulation. They are not intended for user usage.

If you need to something on the file system, I suggest you use SSIS or SQL Agent.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HafeezUddin-8965 avatar image
1 Vote"
HafeezUddin-8965 answered
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

I would agree with Hafeez that using sys.dm_os_file_exists is a better choice, since it is easier to use programmatically. But it is equally undocumented as xp_fileexists.

Nevertheless, there is a solution. Put the operation you want to conduct in a stored procedure, probably including at least the part of the folder to where the user can look for a file. That is, the user should not be able to interrogate the entire server.

Next you create a certificate and sign the procedure with the certificate. Then you create a login from the certificate and make that login member of sysadmin. That "login" is not a real login that can log in, it exists only to connect certificate and permission.

I discuss this technique in a lot more detail in the article (Packaging Permissions in Stored Procedures](https://www.sommarskog.se/grantperm.html) on my web site.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.