T-SQL code to see Windows Folder access Does anyone know how to check from T-SQL code in a stored procedure if a SQL user have read access to a WIN folder that is mapped to the SQL server? Thx Erik
SQL Server doesn't have (nor would you want it to have) native file system access built into Transact SQL. However, you can use the CLR to build a function that does specifically what you need to do and then call that CLR function from a T-SQL Stored Procedure.
See the Microsoft docs for CLR User Defined Functions for information on how to go about creating a CLR UDF.
Note that security is going to be an issue and you're going to have to make sure that your UDF isn't able to be exploited. Your SQL Agent service is going to need the necessary permissions to execute your UDF and your DBA may shoot down the idea because (a) they don't want the exposure of giving the SQL Agent service more permissions and (b) they may not like CLR UDFs as a matter of principle.
Depending on how dynamic your user permissions are, you might instead consider creating a scheduled process that reads the user permissions from your folders and records the results in a table that can be read using garden variety T-SQL when necessary.  The scheduled process could be a simple .NET Windows service or maybe a PowerShell script which is independent of your SQL Server, except that it writes results into a table. This lets you isolate the security contexts better and will probably be an easier sell to DBAs and your InfoSec folks.