classic asp recordset permissions problem

Question

I'm trying to return values of a specific column using the below sql string, If I change it out to sql = "select * from a_page" and objRS("P_description") it returns the values but for some reason my page will not load when using the below code.

UPDATE: I turned off on error resume next and the error I'm receiving is select permission denied. How would I give myself permissions with the code below?

SQL = "select P_Name as P_Name, P_Description as P_Description from L_PagePermission inner join A_Permission on p_permissionID = pp_PermissionID inner join A_Page on P_PageID = PP_PageID where P_PageID = 84 order by p_Name"

Page_ID = 84

connectionstring = obj_ADO.getconnectionstring

Set objCon = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
objCon.Open connectionstring

SQL = "select P_Name as P_Name, P_Description as P_Description from L_PagePermission inner join A_Permission on p_permissionID = pp_PermissionID inner join A_Page on P_PageID = PP_PageID where P_PageID = 84 order by p_Name"

objRS.open SQL, objCon

objRS.MoveFirst
while not objRS.EOF
    response.Write objRS("P_Name")
    objRS.MoveNext
wend

objRS.close
objCon.close

Question 1: Do the values of P_Description contain special characters such as <, >, &, which may be interpreted as HTML? Question 2: Do you use "On Error Resume Next" in your script? If yes, you might get better error messages if you comment that line out. — , Jun 22 '11 at 06:44
I removed the on error resume next and the error I'm receiving is ]SELECT permission denied on object 'A_Permission', database. Thanks that on error resume next was a huge problem — kurupt_89, Jun 22 '11 at 07:11
What database type are you using? And what does the connection string look like (with the actual u/p, and server name redacted natch.) — AnonJr, Jun 23 '11 at 00:36
Its Microsoft sql server with a connection string that looks simlar to this "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User ID=xxxx;Initial Catalog=xxxx;Data Source=xxxx" — kurupt_89, Jun 23 '11 at 05:01
Not exactly I'm not at work experience ATM so that's what I remember it looked like — kurupt_89, Jun 23 '11 at 05:02
What exact line is causing the error and what is the exact error message? — Shadow The GPT Wizard, Jun 23 '11 at 08:07
Please notify with `@` when you reply to comments e.g. @namehere otherwise people won't see you replied and won't come back. — Shadow The GPT Wizard, Jun 23 '11 at 08:08
@ShadowWizard the line that is causing the error is SQL = "select P_Name as P_Name, P_Description as P_Description from L_PagePermission inner join A_Permission on p_permissionID = pp_PermissionID inner join A_Page on P_PageID = PP_PageID where P_PageID = 84 order by p_Name", the error message was select permission denied I cant tell you the exact message until the next time I go into the office in a few days unfortunately. — kurupt_89, Jun 23 '11 at 10:55

score 1 · Accepted Answer · answered Jun 23 '11 at 11:12

The error you get plus the code you mentioned that is working means one thing: on either L_PagePermission table or A_Permission (or both) the user passed in the connection string has no Read permissions.

To solve this, you have to either pass "stronger" user in the connection string, or grant Read permissions to the user over those tables via something like SQL Management Studio.

By the way, you can't "grant yourself permissions" through code due to obvious security reasons - permissions exist to prevent code from doing certain things in the first place.

classic asp recordset permissions problem

1 Answers1