权限否认SQL查询 [英] Permissions denied on sql query

问题描述

我试图通过传统的ASP记录，执行以下查询 -

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提供程序的ODBC
  驱动程序错误80040E09

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

[微软] [ODBC SQL服务器
  程序] [SQL服务器] SELECT权限
  拒绝了对对象'A_Permission'，
  数据库'HRWB_3_0，架构DBO。

[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.

推荐答案

你不会是能够得到解决这个权限错误，除非你授予选择访问 L_PagePermission 和 A_Permission 表到登录您正在使用连接到数据库，或者，除非您使用已经具备了这些表选择访问一个不同的登录。

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.

另一种方法是写一个新的存储过程，并授予执行访问该存储过程。授予在任何情况下的权限SQL很简单：

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:

要授予一个表的SELECT权限：
GRANT SELECT ON [表名]到[登录名]

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

要授予执行访问存储过程：
GRANT EXECUTE ON [PROCEDURENAME]到[登录名]

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

还有一个方法，可以工作，但有明显的安全隐患是添加您正在使用该数​​据库的db_owner角色登录。这应该工作，但不建议除非你是舒适与安全风险presents。

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.

这篇关于权限否认SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！