Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cross database view/table permissions Expand / Collapse
Author
Message
Posted Monday, August 11, 2014 1:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:13 PM
Points: 73, Visits: 402
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!
Post #1602034
Posted Monday, August 11, 2014 1:30 PM This worked for the OP Answer marked as solution


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 3:28 PM
Points: 817, Visits: 743
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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1602036
Posted Monday, August 11, 2014 1:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:13 PM
Points: 73, Visits: 402
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?
Post #1602043
Posted Monday, August 11, 2014 2:07 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 26, 2014 3:28 PM
Points: 817, Visits: 743
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



Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1602050
Posted Monday, August 11, 2014 3:18 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 23, 2014 3:13 PM
Points: 73, Visits: 402
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!).

Post #1602085
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse