July 11, 2012 at 1:18 pm
When a non-sysadmin sql user executes the xp_dirtree in a specific directory on a local server, it doesn't return any records. When sysadmin permission is added to the same user, it returns the file names and works fine. How can I make this user return the file names without giving the full sysadmin permission? I'm fine with granting this user higher permissions but not the full sysadmin. Thanks!
For example,
EXEC Master.dbo.xp_DirTree '\\Myserver\myshare\',1,1
With sysadmin permission, returns
subdirectory depth file
-------------------------------------
mytestfile.txt 1 1
mytestfile2.txt 1 1
(2 row(s) affected)
Without sysadmin permission, returns
subdirectory depth file
-------------------------------------
(0 row(s) affected)
July 11, 2012 at 1:32 pm
What's the service account for sqlserver?
This can sometimes have an effect on the output of xp_dirtree.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 11, 2012 at 1:52 pm
The service account ADMIN has full rights to the Windows file server. If I use any sql account (like sa) that has sysadmin permissions to run the xp_dirtree, it will work. Anything less appears to not able to access the file server at all...something missing that needs to be setup I guess? Thanks!
July 11, 2012 at 1:54 pm
I think it is an expected behavior from this undocumented procedure.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply