SQL File List No Results via Web Page or Execute As

Posted on 2013-09-09
Medium Priority
Last Modified: 2013-09-16
I have a stored procedure that gets data from SQL and also executes xp_dirtree to gather a file list, for linking. This works great when I execute the main stored procedure from SQL Mgmt Studio. However, when I try to execute it from the web page that dumps it into a .xlsx file, it does not give me the file list. (Whereas, the end users can choose the parameters they want for the results.)

1. Again, when I run via SQL it works fine.
2. The parameters being passed from the web page are correct.
3. I have given the domain account that runs SQL 'read' access to the directories and share. (Whereas, I have read this could be the issue)
4. I have tried to 'Execute as' when executing the xp_dirtree. (Doing this fails to give me a file list from within SQL Mgmt Studio)

Any advise would be helpful.
Question by:randhammack
  • 2
  • 2
LVL 35

Expert Comment

by:David Todd
ID: 39478505

Presumably when running from SSMS you have logged on with sa permissions.

What rights (in SQL) does the account get when running from the web page?

LVL 52

Expert Comment

by:Mark Wills
ID: 39478721
Agree with David...

It will most likely be how the web app connects to your environment and then how that in turn connects to SQL Server.

So, if you can run it locally, it probably means you have the correct mapping and permissions. But your web user doesn't.

Unfortunately, xp_DirTree is one of those "unofficial" extended stored procedures.

So, for a start it will be hiding in master and you might need access or explicitly tell your routine to use master.

use master

select sysusers.name, sysobjects.name, sysobjects.id 
from sysobjects, sysusers, sysprotects 
where sysobjects.id=sysprotects.id and sysprotects.uid = sysusers.uid 
and sysobjects.name =  'xp_dirtree' 

select * from syscomments where id = object_id('xp_dirtree')

Open in new window

and from the above, you will see that it uses a DLL to execute, so, that will be something else to check up.

Ideally you use supported techniques such as xp_cmdshell to get your list...

Accepted Solution

randhammack earned 0 total points
ID: 39480947
I tried using xp_cmdshell it will not work once within the StProc.

It only seems to work using the xp_dirtree and when I set the domain account I am using for the website as a 'sysadmin', which I prefer not to do. Didn't even work just by granting execute permissions on xp_dirtree to the account.
LVL 52

Expert Comment

by:Mark Wills
ID: 39482880
Ummm... can usually get xp_cmdshell to work especially when protected inside a stored procedure.

Author Closing Comment

ID: 39495644
Just going to give the access to that login.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question