2

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

Improve this question
10
  • Does the user exist at the DB level. Open your database -> Security -> Logins and make sure the user exists at the DB level, if it doesn't, you'll need to add that user to the DB so you can then grant it permissions. Also, your use of the word 'schema' isn't correct. In 3-part naming it's [DBNAME].[SCHEMA].[OBJECT]. In this case, your schema is dbo.
    – Kris Gruttemeyer
    Commented Mar 30, 2015 at 20:36
  • A user doesn't exist in a schema, so can you better explain what you mean by "UserB does exist on SchemaB"?
    – Aaron Bertrand
    Commented Mar 30, 2015 at 20:37
  • Kris / Aaron: The userB does exist in databaseB and it's able to insert / update TableB. My problem is that now I have to add a trigger that will update tableA on databaseA and I'm failing to accomplish that.
    – Plicatibu
    Commented Mar 30, 2015 at 20:45
  • Does it exist in DatabaseA?
    – Kris Gruttemeyer
    Commented Mar 30, 2015 at 20:45
  • Kris, UserB doesn't exist on table A. Am I obligated to add it? Cannot I just grant him rights to access resources on databaseA?
    – Plicatibu
    Commented Mar 30, 2015 at 20:47

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.