Permissions denied on sql query

Question

I'm trying to execute the following query through classic asp recordset -

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 = 85 
       order by p_Name"   

Although I've ran into a problem with permissions. So the error that i am receiving is -

Microsoft OLE DB Provider for ODBC Drivers error '80040e09'

[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT permission denied on object 'A_Permission', database 'HRWB_3_0', schema 'dbo'.

How would I go about executing this query without changing permission settings. How can I do this with a stored procedure? (Can someone provide an example too)

I can't change the database settings I have to deal with what I got. I've looked through a lot of the websites files and it seems to be mostly dependent on stored procedures.

Solution

You're not going to be able to get around that permissions error unless you grant select access on the L_PagePermission and A_Permission tables to the login that you are using to connect to the database, or unless you use a different login that already has select access to those tables.

Another approach would be to write a new stored procedure and grant EXECUTE access to that stored procedure. The SQL to grant permissions in either case is simple:

To grant SELECT access to a table: GRANT SELECT ON [TableName] TO [loginName]

To grant EXECUTE access to a stored procedure: GRANT EXECUTE ON [procedureName] TO [loginName]

One more approach that could work but has obvious security implications is to add the login you are using to the db_owner role for that database. That should work, but is NOT recommended unless you are comfortable with the security risks that presents.