Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SSL
Starting Member
4 Posts |
 Posted - 2005-08-11 : 15:23:42
|
| Hi, I'm new to ASP and was fixing some bugs in the ASP pages. We have a production web server and another one for development. I was working against the devleopment and couple of the pages has a call to the same stored Proc and it works in one and it gives and error on the other. Main page displays the result of the stored procedure and there is a link "Export to Excel" which calls another page and it uses the same connection. But it gives and Error- Microsoft OLE DB Provider for SQL Server error '80040e09'Select Permission denied on object 'Region', database 'Intranet',owner 'dbo'/X_Reporting/ExcelGens/DivSumExcelGen.asp, Line 51Both the pages uses the same connection and I tried the output from the webpage on the Analyzer window for this user and it works fine. I'm lost. Any help is appreciated. |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-11 : 15:36:13
|
| Sounds like your SQL user has permissions to EXECUTE the stored procedure used by Page 1, but not the SELECT statement used on Page 2. If the stored procedure for Page 2 uses Dynamic SQL (i.e. and EXEC or sp_executesql) statement, then the user MUST have SELECT permissions on the base tables and not just EXECUTE permissions on the stored procedure.---------------------------EmeraldCityDomains.com |
 |
|
|
SSL
Starting Member
4 Posts |
Posted - 2005-08-11 : 15:52:23
|
| No both the pages uses the same method -try to open a recordset and both calls the same procedure with the same parameters. Here is the piece of code.Set rsGenExcel=Server.CreateObject("ADODB.Recordset")rsGenExcel.CursorLocation = adUseClientsqlStr="usp_UsageByDivision " & sqlVarsResponse.Write sqlStr'Response.EndrsGenExcel.Open sqlStr, NexCon, adOpenForwardOnly, adLockReadOnly, adCmdTextAnd it fails exactly on the recordset open. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-08-11 : 16:50:48
|
| Are you sure it's failing where you think it is? Are there any other ADO objects used in the second page? It doesn't make sense to have two pages that do exactly the same thing, so what's the difference between them? What does the "Export to Excel" page do that the first page does not? What code is accessing this object named "Region"?---------------------------EmeraldCityDomains.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-08-11 : 16:52:43
|
| SSL,Please post the stored procedure code.Tara |
|
|
|
SSL
Starting Member
4 Posts |
Posted - 2005-08-12 : 11:34:05
|
| The first page displays the results on a web page and that page has a link "Export to excel" and this uses the second page. What the second page does is, it gets the recordset and it writes into a Excel Sheet.The SQL Proc works fine in the first page. And just an info the REgion table is on another database and this stored proc references that table. There are similar pages for other reports and they too use this table and all other Export seems to work fine. Could there be any setting on the Web server or something that errs this out?I have checked all the permissions for the users on both the DBS and they seem fine even copied the output from the PAge to the Query Anlayzer as that user and it seems to work fine.Here is the sqlvars variable from the ASP page.sqlVars= " '" & BeginDate & "'" & "," & "'" & EndDate & "'" & ", '" & Request.Cookies("ReportTypes")("DivNum") & "'," & Request.Cookies("ReportTypes")("RateId") Here is the sqlstr from the page after concatenationusp_UsageByDivision '6/1/2005','6/30/2005', '',987952136 |
|
|
|
SSL
Starting Member
4 Posts |
Posted - 2005-08-12 : 14:23:23
|
| I found a fix. I just substituted Set rsGenExcel= NextelCon.Execute(sqlStr) instead of the rsgenexcel.Open statement and it worked just fine. Thanks for all your input and time. |
|
|
|
|
|
|
|
|
SQL Error '80040e09'