Adamus Turner


Greetings all,

What are the prerequisites for enabling and disabling a trigger inside a stored procedure.

I'm running the following code and my application is saying: "User does not have permissions to perform this operation on table DockCrewImportErrors"

ALTER TABLE DockCrewImportErrors

DISABLE TRIGGER trg_UpdateValidation

It runs without error in query analyzer when I call the stored procedure with the above code inside.

I granted permissions to Public on the table.

What am I missing

Thanks,

Adamus





Re: Permissions Enable Disable Trigger inside a store procedure

Arnie Rowland


Does the user account that is causing the TRIGGER to fire have appropriate permissions to the table to disable the TRIGGER.

FROM BOL:

To disable a DML trigger, at a minimum, a user must have ALTER permission on the table or view on which the trigger was created.







Re: Permissions Enable Disable Trigger inside a store procedure

Adamus Turner

Good question.

At this point, I'm going to guess there is no ALTER permission set.

If my account has permissions, can I GRANT this permission to the user account

If so, could you provide the T-SQL code

Thank you,

Adamus







Re: Permissions Enable Disable Trigger inside a store procedure

Arnie Rowland

If your account is in the sysadmin or db_owner roles, or is the owner of the table, or has been GRANTed permission with the permission to subseqeuntly GRANT, then yes.

However, is that a good idea Do you really want users in your database with the ability to change your table schema I know that you most likely have a decent security model, but there could easily be some as of yet unknown exploit that could allow a mal-intended user to cause havoc in your database.

GRANT ALTER ON Object::{YourTable} TO {User}






Re: Permissions Enable Disable Trigger inside a store procedure

Adamus Turner

The database is only touched by an application with no text fields. There is no ability to access the database outside of query analyzer which only has a few accounts configured for the database.

The DBA gave us (a small department) a database to upgrade the current system from Access to Vb.net/SQL

I am the sole contract programmer in the department and there is no data entry on any form.

I'm guessing once my project is complete, the Windows authentication with be disabled and only 1 SQL account will remain.

Adamus






Re: Permissions Enable Disable Trigger inside a store procedure

Adamus Turner

It does not appear to have ALTER permissions and I do not have sysadmin permissions.

The account is not the owner of the of the table but my NT login is the owner.

If I log into Query Analyzer with the SQL account and recreate the table so the table now belongs to the account, will this resolve the issue

Adamus






Re: Permissions Enable Disable Trigger inside a store procedure

Arnie Rowland

Yes, if that SQL Account is the one is the proxy account for the applications users. That 'should' solve the problem.




Re: Permissions Enable Disable Trigger inside a store procedure

Adamus Turner

That worked perfectly!

I dropped the table and recreated it with account that was accessing it via code.

Thanks,

Adamus