What database user permissions are needed?

Question

FYI: 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.

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.

Answer 1

The user will "at a minimum" require ALTER permissions on the table in question. See: http://technet.microsoft.com/en-us/library/ms182706.aspx

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

http://msdn.microsoft.com/en-us/library/ms188354.aspx