Cross database view/table permissions

Question

Cross database view/table permissions

BowlOfCereal

SSCrazy

Points: 2008
More actions
August 11, 2014 at 1:18 pm

#397181

Hi all,
We have two databases which contain a series of tables and views. The views select data from the tables in their own individual database. All objects are in the dbo schema; we don't use other schemas. The same users exist in both databases, and are members of an identical role which exists in both databases. In both databases, the role has been granted SELECT permissions to the views, but no permissions to the base tables themselves. This all works very well; in each database, the users effectively can access data through the views, but not from the base tables.
However, now we have a new view in database A which selects from base tables in database B. Granting the usual SELECT permissions on this view does not work -- the users get an error saying the SELECT permission was denied on the table in database B.
I suspect this is a cross-database / ownership chaining issue, but I can't find a proper solution. I tried setting the TRUSTWORTHY and DB_CHAINING options in both databases as a scattershot approach, but it didn't change the error message. I don't want to grant permissions to the base tables in either database, although I suspect that may get around the error.
How can I grant these permissions (and/or configure the system) so that the users can still only SELECT from the views and not the base tables in both databases?
Thanks in advance!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 1

For DB_CHAINING to work the two databases must have the same owner.

For TRUSTWORTHY to work, the databases must either have the same owner, or the owner of the database where the referencing view is must have been granted AUTHENTICATE permission in the target database.

Both these options opens for the persons in the db_owner role in the respective databases to do things in the other database which maybe they shouldn't. That is, if you have persons who only have db_owner rights in one of the databases.

Had it been a question of a stored procedure, you could have used certificate signing for more fine-grained permission, but as I recall this does not work for views.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

BowlOfCereal SSCrazy Points: 2008 More actions · Answer 2

Thank you for the quick reply! I found that indeed, the databases had different owners. I changed the owner on one database so that they're in synch, then turned off the TRUSTWORTHY and DB_CHAINING options in each database and turned them on again. Unfortunately, the symptoms didn't change -- users querying the view in database A are still getting the error complaining about permissions to the base table in database B.

Any other thoughts?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 3

So there is something you are not telling us. Here is a script that you can play with, that shows how it works with DB_CHAINING. (Note that you don't need both of DB_CHAINING and TRUSTWORTHY.)

CREATE LOGIN dbowner WITH PASSWORD = '()M*^23VJHKDAG'

CREATE LOGIN plainuser WITH PASSWORD = '/%%"#$#DGS1'

CREATE DATABASE db1

CREATE DATABASE db2

ALTER DATABASE db1 SET DB_CHAINING ON

ALTER DATABASE db2 SET DB_CHAINING ON

ALTER AUTHORIZATION ON DATABASE::db1 To dbowner

ALTER AUTHORIZATION ON DATABASE::db2 To dbowner

go

USE db1

go

CREATE TABLE table_in_db1(a int NOT NULL)

INSERT table_in_db1(a) VALUES (99522)

CREATE USER plainuser

go

USE db2

go

CREATE USER plainuser

go

CREATE VIEW db2_view AS SELECT a FROM db1.dbo.table_in_db1

go

GRANT SELECT ON db2_view TO plainuser

go

EXECUTE AS LOGIN = 'plainuser'

go

SELECT a FROM db2_view

go

REVERT

go

USE tempdb

go

DROP DATABASE db1

DROP DATABASE db2

DROP LOGIN dbowner

DROP LOGIN plainuser

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

BowlOfCereal SSCrazy Points: 2008 More actions · Answer 4

Thank you!! Your sample script was quite helpful, and worked perfectly well. I resolved the problem in my own case when I reset the database owner using the ALTER AUTHORIZATION commands in your script. I had previously used the old deprecated sp_changedbowner syntax -- either it hadn't worked or I hadn't run it correctly. Anyway, I reset the owner again and my new view started working correctly. Thank you for the help (both here and for your execellent articles over the years!).