What Access Rights am I Missing on my Stored Procedure

Question

I'm trying to run a stored procedure from my website that disables a trigger. Here is the code for the trigger:

CREATE PROCEDURE [dbo].[DisableMyTrigger]
AS
BEGIN
    alter table dbo.TableName DISABLE TRIGGER TriggerName
END

I've also set the permissions on the stored procedure with:

Grant Exec on dbo.DisableMyTrigger To DBAccountName

DBAccountName is and has been able to run other stored procedures as well as dynamic SQL statements without issue.

Here is the code from my CFM page:

<cfstoredproc datasource="myDatasource" procedure="DisableMyTrigger" />

And here is the error I'm getting:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][SQL Native Client][SQL Server]Cannot find the object "TableName" because it does not exist or you do not have permissions.

Answer 1

Its because your connected to the wrong db Catalog (assuming SQL Server). Your connected to the database but your connected to the "default" catalog, which is probably "master"

I think you can put something in the beginning of your procedure like :

use DBName;

and that will connect you to the proper catalog.

Answer 2

You may have to specify the database. I don't think it's a permissions issue, i believe it just can't find it in that database.

Answer 3

Does DBAccountName have permissions to TableName? These can be granted or revoked separately from the overall schema (dbo).

I'm not a dba, but is DBAccountName allowed to execute DDL statements? (so it can do things like disable triggers programmatically)