FYI: SQL Server 2005
供参考:SQL Server 2005
I have a database user account (user_web) that has the ability to connect to and run queries and stored procedures in my database. Specifically, I have given the user the db_datareader and db_datawriter roles as well as granted them execute permission on the specific stored procedures it needs to be able to run.
我有一个数据库用户帐户(user_web)，它能够连接和运行数据库中的查询和存储过程。具体地说，我已经为用户提供了db_datareader和db_datawriter角色，并允许它们在需要能够运行的特定存储过程上执行权限。
In one of the stored procedures, I need to disable a trigger then re-enable it after some specific edits are done. When I attempt to run that stored procedure with the user I get the following error:
在其中一个存储过程中，我需要禁用触发器，然后在完成某些特定的编辑之后重新启用它。当我试图与用户运行该存储过程时，我得到以下错误:
Cannot find the object "TableName" because it does not exist or you do not have permissions.
TableName is the table where I am attempting to disable and enable the trigger. My question is what is the least amount of permissions I can give to my user account that will allow it to successfully run the stored procedure.
TableName是我试图禁用和启用触发器的表。我的问题是，我能给我的用户帐户的权限中，允许它成功运行存储过程的权限最少是多少。
2 个解决方案
#1
2
The user will "at a minimum" require ALTER permissions on the table in question. See: http://technet.microsoft.com/en-us/library/ms182706.aspx
用户将“至少”要求修改相关表上的权限。参见:http://technet.microsoft.com/en-us/library/ms182706.aspx
#2
2
Rather than grant the user ALTER permissions on the table, which could be a security issue, I'd have that particular stored procedure run as a different user that does have those permissions. Use the EXECUTE AS syntax to accomplish this.
与其授予表上的用户ALTER权限(这可能是安全问题)，不如让具有这些权限的另一个用户运行特定的存储过程。使用EXECUTE作为语法来实现这一点。
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://msdn.microsoft.com/en-us/library/ms188354.aspx
#1
2
The user will "at a minimum" require ALTER permissions on the table in question. See: http://technet.microsoft.com/en-us/library/ms182706.aspx
用户将“至少”要求修改相关表上的权限。参见:http://technet.microsoft.com/en-us/library/ms182706.aspx
#2
2
Rather than grant the user ALTER permissions on the table, which could be a security issue, I'd have that particular stored procedure run as a different user that does have those permissions. Use the EXECUTE AS syntax to accomplish this.
与其授予表上的用户ALTER权限(这可能是安全问题)，不如让具有这些权限的另一个用户运行特定的存储过程。使用EXECUTE作为语法来实现这一点。
http://msdn.microsoft.com/en-us/library/ms188354.aspx
http://msdn.microsoft.com/en-us/library/ms188354.aspx