XP_DirTree in SQL Server

Question

Different options were asked. I have no problem finding in the local directory with the code snippet below.

EXEC MASTER.sys.xp_dirtree 'C:\', 1, 1 

When I switch the path to the network location, the results are empty.

 EXEC MASTER.sys.xp_dirtree '\\Server\Folder', 1, 1 

At first I thought it might be related to permissions. I added the SQL Server service to the ACLs on the shared volume, as well as to the security group.

Any help or direction to point me is much appreciated or even another way to get a list of files in a directory and subdirectories.

[Changed]

Answer 1

Two things to look out for:

• Verify that the logon account for the SQL Server service (the service usually listed as "SQL Server (MSSQLSERVER)" in the "Services" list) has rights to this network resource.

  UPDATE

  The problem was that the OP started the SQL Server service as a local system account. In this way, the OP created a domain account for SQL Server, assigned a new domain account to the Logon account for SQL Server, and granted that domain account proper NTFS permissions.

  Note that this can also be fixed when saving the SQL service running as a local system account by adding the server itself, on which SQL Server runs with NTFS permissions. This is usually possible by specifying a server name followed by a dollar sign ($). For example: MySqlServer01$ . Of course, then this gives NTFS permission to all services on this server that operate as the local system account, and this may not be desirable. Therefore, it is still preferable to create a domain account for the SQL Server service, which will work as (which is good practice anyway!).

  It seems that this was done, so it needs to be tested by going into the windows directly as this account and trying to navigate to this particular network path.

• Make sure you have sysadmin privileges to log into SQL Server running xp_dirtree :

  • This can be done by adding an account to the sysadmin server sysadmin or

  • Enter the stored procedure that runs xp_dirtree :

    • Create certificate in [master]
    • Create a login based on this certificate
    • Add certificate-based login to sysadmin server sysadmin
    • Certificate backup
    • Recover a certificate in any database or will have a stored procedure that runs xp_dirtree
    • Sign a stored procedure that starts xp_dirtree using ADD SIGNATURE and a newly restored certificate
    • GRANT EXECUTE on this stored procedure for users and / or roles (s) that must perform this.

To state this, another option is to completely abandon xp_dirtree and use SQLCLR instead. Probably C # code example on different blogs. There are also several CodePlex projects that have file system functions, and may also include building a compiled assembly for those who do not want to do compilation. In addition, there is an SQL # library that has several file system functions, including File_GetDirectoryListing , which is TVF (which means: you can use it in a SELECT statement with the WHERE clause, instead of dropping all columns and all rows to temp table first) . It is also fully streaming, which means it is very fast, even for files of 100 KB or more. Please note that the FILE_* functions are only in the full version (i.e. Not free), and I am the creator of SQL #, but it copes with this situation perfectly.