Trigger - across databases rights?

16 Sep 2005 11:26 AM

Henry

Hi All

I have a trigger which is copying data from a tabel in database A into
another table in Database B on the same SQL server - but for some reason
fails, without an error (as far as I can see).

As soon as I change the trigger to copy in to a tabel in the same database A
as the source table everything works fine.

It used to work between the databases on my all my Lab environment and on
several customer sites - but on a new system (3rd. party which generated the
data I need to copy) I'm testing it doesn't work anymore.
I running MSSQL 8.00.761 (SP3).

I have granted access to public on the destination database B as well as on
the destination table.

What am I missing?

regards
Henry

16 Sep 2005 11:39 AM

Brian Selzer

In order to gain access to a table in a database, you a login in the
database and sufficient access to the tables. I suspect you need to add a
login to the other database.

Show quote

"Henry" <n**@il.com> wrote in message
news:#MWe0FruFHA.3864@TK2MSFTNGP12.phx.gbl...
> Hi All
>
> I have a trigger which is copying data from a tabel in database A into
> another table in Database B on the same SQL server - but for some reason
> fails, without an error (as far as I can see).
>
> As soon as I change the trigger to copy in to a tabel in the same database
A
> as the source table everything works fine.
>
> It used to work between the databases on my all my Lab environment and on
> several customer sites - but on a new system (3rd. party which generated
the
> data I need to copy) I'm testing it doesn't work anymore.
> I running MSSQL 8.00.761 (SP3).
>
> I have granted access to public on the destination database B as well as
on
> the destination table.
>
> What am I missing?
>
> regards
> Henry
>
>

16 Sep 2005 12:02 PM

Henry

> In order to gain access to a table in a database, you a login in the
> database and sufficient access to the tables. I suspect you need to add a
> login to the other database.

Wasn't needed before or in my LAB, I wonder what has been changed in MSSQL
or the 3rd party system?

regards
Henry

16 Sep 2005 11:41 AM

Jens

you have to enabled crossownership chain. If the two objects then
belong to the same owner in both databases and the owner has access to
the tables the insert will work.

Read more on:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_4iyb.asp

HTH, Jens Suessmeyer.

16 Sep 2005 2:38 PM

Henry

> you have to enabled crossownership chain. If the two objects then
> belong to the same owner in both databases and the owner has access to
> the tables the insert will work.

I have enabled that and restarted the MSSQL but no change, there must be
something I don' see, it still doesn't work.

this works
INSERT INTO AlarmQueue

This doesn't
INSERT INTO WRSDB.[dbo].AlarmQueue

Also, I have set the dbowner to the same account but with no success.
All existing accounts have been granted "select, update etc".

Any other suggestions?
Henry

16 Sep 2005 3:01 PM

Dan Guzman

> This doesn't
> INSERT INTO WRSDB.[dbo].AlarmQueue
>
> Also, I have set the dbowner to the same account but with no success.
> All existing accounts have been granted "select, update etc".

So you're saying that the account used for the insert has direct permissions
on the WRSDB.[dbo].AlarmQueue table but you are getting an 'INSERT
permission denied' error? That doesn't make sense, assuming the account
used is one of those that were granted permissions and your logins are
correctly mapped.

Without direct permissions, I'd expect the insert to succeed by following
the instructions in the link Jens posted. In summary:

1) both databases have the same owner as reported by sp_helpdb.

2) the 'db chaining' database option is enabled in both databases or the
server-level 'cross database chaining' is enabled

3) the user has access to both databases

If you still have problems, please post the actual error message.

--
Hope this helps.

Dan Guzman
SQL Server MVP

Show quote

"Henry" <n**@il.com> wrote in message
news:eBY9UxsuFHA.3152@TK2MSFTNGP12.phx.gbl...
>> you have to enabled crossownership chain. If the two objects then
>> belong to the same owner in both databases and the owner has access to
>> the tables the insert will work.
>
> I have enabled that and restarted the MSSQL but no change, there must be
> something I don' see, it still doesn't work.
>
>
> this works
> INSERT INTO AlarmQueue
>
> This doesn't
> INSERT INTO WRSDB.[dbo].AlarmQueue
>
> Also, I have set the dbowner to the same account but with no success.
> All existing accounts have been granted "select, update etc".
>
> Any other suggestions?
> Henry
>
>
>

16 Sep 2005 4:47 PM

Henry

Show quote

>> This doesn't
>> INSERT INTO WRSDB.[dbo].AlarmQueue
>>
>> Also, I have set the dbowner to the same account but with no success.
>> All existing accounts have been granted "select, update etc".
>
> So you're saying that the account used for the insert has direct
> permissions on the WRSDB.[dbo].AlarmQueue table but you are getting an
> 'INSERT permission denied' error? That doesn't make sense, assuming the
> account used is one of those that were granted permissions and your logins
> are correctly mapped.
>
> Without direct permissions, I'd expect the insert to succeed by following
> the instructions in the link Jens posted. In summary:
>
> 1) both databases have the same owner as reported by sp_helpdb.
>
> 2) the 'db chaining' database option is enabled in both databases or the
> server-level 'cross database chaining' is enabled
>
> 3) the user has access to both databases
>
> If you still have problems, please post the actual error message.
>

Hi Dan

Assumptions are the mother of all f.... ups

I assumed that the error were the same, but after fiddling around with trace
in profiler I found the error was another that before, it's was simply a
(faulty) NULL value in one of the fields the trigger had to copy - table
structure was supposed to be identical - wrong again :(

Obviously the error handling in the trigger fails

CREATE TRIGGER trAlarmsInsert
ON Alarm
AFTER INSERT
AS
BEGIN
DECLARE @rows int
DECLARE @err int
DECLARE @ErrMsg varchar(200)

-- SNIP---
create trigger....
-- SNIP---

SELECT @rows = @@ROWCOUNT, @err = @@ERROR
IF @rows < 1 or @err <> 0
begin
   SET @ErrMsg = 'Failed to update alarm queue. '
       + 'Rows:' + cast(@rows as varchar(50))
       + ', Err:' + cast(@err as varchar(50))
      RAISERROR ( @ErrMsg, 0, 1 ) WITH LOG
END

Nothing in the application log.

Sorry for waisting your time.
Henry