Trigger - Not a Valid User

[jennuhw]

I am testing the trigger listed below to update another database:

CREATE TRIGGER [ESP_Global_Quote_Table] ON [oe_hdr]
FOR INSERT
AS

/*
The trigger will enter order number and item id into the quote table in
GlobalOpp database on ESP12.

*/
declare @order_no as varchar(8)
select @order_no = order_no from inserted

if @@ROWCOUNT <> 0
Begin
declare @item_id as varchar(40)

SELECT @item_id = item_id, @order_no = order_no
FROM p21_order_view where order_no = @order_no
IF @@ROWCOUNT <> 0
RETURN
BEGIN
insert into ESP12.GlobalOpp.dbo.Quotes (order_no, item_id) values (@order_no, @item_id)
END

End


It is currently on the same server (ESP12), but I am still getting that the domain user is not a valid user for the database GlobalOpp. I have tried putting my username in for the Users, and Domain Users is in there also. I have set permissions for both, but I still get this error.
In the end, I am going to move this to the production server which is different. I did see that I would need to create a job to do this, but I can't figure out how to transfer the value of the order number to the job. Any help would be greatly appreciated. Thanks!

 

[druer]

I am certainly able to have a trigger insert values into another database on the same server.

I think the issue may be:
1. You don't need the server name qualifications at the beginning of the command for ESP12.
2. It only works for me when the database name is absolutely spelled identical (case sensitive.)
3. .dbo. may not be the owner of that table in your other database.
4. The message is accurate and the system is using NT security and your username was not added as a login to the other database, or your userid was purposely limited/restricted for that database. (I couldn't tell if you were saying you adding your name as a login, in the GlobalOpp database, or if you meant you changed the .dbo. to your name or something.)
5. I've never tried to know, but is it possible that you have ESP12 as a linked server back to itself with a different database. So when you are qualifying ESP12 it is trying to use the Linked Server connection instead and whatever database is defaulted is the one you don't have access to?

Lots of guesses

 

[jennuhw]

We have a duplicate test database on ESP12, and that is what I currently have the trigger setup on.

1, 2 and 3: I did take out the server name qualifications and found out that dbo didn't 'own' the database.
4. I was adding my nt login as a user of the database which is also a member of Domain Admins. I have full access of the db and gave myself full rights.
5. I haven't actually used the linked server yet. I am hoping to get it to work on one server before trying to get it to work remotely.

I am still getting the same error. Thanks so much for your help so far.

 

[druer]

Did you try the command with the proper owner for the database? ... GlobalOpp.RealOwner.Quotes ...

 

[jennuhw]

Yes, I did try that. I am guessing it is one little thing that isn't right, but I have no idea what! Thanks.

 

[druer]

Since you have "all" privelages to the test environment can you go ahead and make DBO the owner and try with .dbo. so that you can eliminate any permissions that are not granted by the "current" owner.

 

[druer]

Is it possible that you are added to the other database as the UserName but not as \\Domain\UserName or something?

 

[jennuhw]

I have created a temp table that is filled with a stored procedure to work around this. It seems to be working very well. Thanks for your help, druer!