Re: Trigger Security Problem

• Previous message: Clifford Heath: "Re: Security and access rights using local and global groups"
• In reply to: Bill Richardson: "Trigger Security Problem"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Tue, 1 Mar 2005 23:18:49 +0100

Hi

Post the Trigger Code and the Table definitions. We need to see what and how
you reference it to help.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Bill Richardson" <BillRichardson@discussions.microsoft.com> wrote in
message news:99DF8DBC-D71A-4F1C-9B36-B64D0C8184C9@microsoft.com...
> I am having problems with triggers being able to "see" other tables in the
> database. I am using SQL Server 2000 SP3
>
> I have written a generic data auditing trigger that works fine on tables
> that I create with the Enterprise Manager signed on as the admin account
> "sa". However, when I put the trigger on a table I create with query
analyzer
> signed on as a Windows account, the trigger creates okay, but fails at
> execution with the error "invalid object name 'Data_Changes'" (Data
Changes
> is the table that the trigger writes to).
>
> I'm thinking this must be a security issue somehow. The "dbo" user is
> defined as the Windows account I am using, which is also the Windows
account
> under which the SQL Server service is running. The Windows account has no
> problems doing normal UPDATEs (on tables without trigggers) to tables I
> create with the "sa" account--only the trigger updates fail. For example,
> signed on as the Windows account, I can do INSERT INTO Data_Changes...
just
> fine. But when a trigger on a table created with the Windows account
fires
> and tries to INSERT INTO Data_Changes, it fails with the error.
>
> Whether I create tables with the "sa" account or the Windows account, the
> tables all show as owned by "dbo".
>
> I am relatively new to SQL Server, having programmed in an Oracle
> environment up to now, so perhaps there is something I don't understand
about
> SQL security and triggers.
>
> Any help will be appreciated. I hope the description above isn't too
> confusing!

• Previous message: Clifford Heath: "Re: Security and access rights using local and global groups"
• In reply to: Bill Richardson: "Trigger Security Problem"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]