SQL Server 2008 R2 – How to Grant Insert in Another Database Table

sql-server-2008-r2

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

GRANT Object Permissions (Transact-SQL)

Setting user permissions for different SQL Server schemas

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.

Any ideas?

Edited: Fixed the naming. It's database. Not schema as pointed out by Kris Gruttemeyer

Best Answer

Indeed what I was looking for (but were unaware of its name) is called cross database ownership chaining.

By reading the article Understanding Cross Database Ownership Chaining in SQL Server I was able to solve my problem.

As a reference, below is what I did:

Use [DatabaseA]
GO

ALTER DATABASE [DatabaseA]
SET DB_CHAINING ON
GO

Use [DatabaseB]
GO
ALTER DATABASE [DatabaseB]
SET DB_CHAINING ON
GO

EXEC sp_grantdbaccess 'UserB';
GO 

GRANT SELECT, UPDATE on [DatabaseB].[dbo].[TableA] TO [UserB]
GO

Thank you all.

Related Question