I am trying have a user capable of running the xp_dirtree extended stored procedure. I only receive output if I make the user a sysadmin. If not granted sysadmin it appears to run with blank output. I do not not to make the user a sysadmin. Is there another
way to grant this user rights to dirtree and receive output.
You write a stored procedure that calls xp_dirtree. Then you create a certificate in the master database which you use to sign the wrapper. Then you create a login from the certificate (this login cannot log in, it's just a holder for permissions). Then
you add the cert login to sysadmin. Finally, you grant permission to the user in question. If you put the procedure in master, you will need to add the user to master, but you could also filter for the particular user in the procedure.