On SQLServer 2008 R2 I have 2 schemas: DatabaseA and DatabaseB.
On DatabaseB I have a table named TableB. Whenever tableB is updated, a trigger will insert a value in the tableA (that is on DatabaseA).
For that I need to grant insert / update on tableA to UserB (that is on DatabaseB).
dbo is the owner of tables on DatabaseA and UserB is a service user of tables on DatabaseB (I mean, UserB is not the owner from tables on DatabaseB. It has limited access to them - For some tables he can insert / update permissions and for other tables it has just select permission).
After reading these articles
I tried the following:
USE [DatabaseA] GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [DatabaseB].[UserB] GO
I got the error: Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '.'.
Then I tried
USE [DatabaseA] GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [UserB] GO
And got message below (what makes sense taking in account UserB doesn't exist on DatabaseA): *Msg 15151, Level 16, State 1, Line 2
Cannot find the user 'UserB', because it does not exist or you do not have permission.*
Then I tried
USE [DatabaseA] GRANT INSERT, UPDATE ON DatabaseA.dbo.TableA TO [DatabaseB\UserB] GO
Which gave me Msg 15151, Level 16, State 1, Line 2 Cannot find the user 'DatabaseB\UserB', because it does not exist or you do not have permission.
UserB does exist on DatabaseB and I'm issuing the command as sa user so I don't get what I'm doing wrong.
Edited: Fixed the naming. It's database. Not schema as pointed out by Kris Gruttemeyer