SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TRUSTWORTHY ON to let sproc execute across Dbs


TRUSTWORTHY ON to let sproc execute across Dbs

Author
Message
Maxer
Maxer
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4610 Visits: 1682
OK.... this problem again Smile

Setup:
Database: AllOurFinancialData

We have an external group who is doing some work for us, they need access to:
SELECT *
FROM AllOurFinancialData.NOTdbo.SuperSecretFinancials
WHERE DeptCode = '12345'

nothing else.

I tried doing the view in a NEW database:
ExternalGroupDb

SELECT *
from AllOurFinancialData.NOTdbo.SuperSecretFinancials
WHERE DeptCode = '12345'


That of course didn't work:
The SELECT permission was denied on the object 'SuperSecretFinancials', database 'AllOurFinancialData', schema 'NOTdbo'.


So, I said no big I'll use Stored Procedure Execute as Owner: our DBA owns both databases.

However, that fails because TRUSTHWORTHY was not set to on for ExternalGroupDb.

I turned Trustworthy on, works great...but.....

IS that the only real option I have? *Not doing cross database ownership chaining, freaks me out...

I could sign the stored procedure with a cert but...that's a PITA....

Why can't I get that view to work?

I don't fully understand that? I tried reading through this, but I think I am a bit slow today (up late with cranky baby last night) http://www.sommarskog.se/grantperm.html

I didn't see a way to get a view working in that article?



I have user ExternalGroupUser in both databases.

That user has SELECT rights on the view I created in the ExternalGroupDb and only public rights to 'SuperSecretFinancials'.

I'm missing something here...yes?

Thanks!

*EDIT: DBA owns both databases, so... same owner there.
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13372 Visits: 879
The alternatives without moving data are:

1) EXECUTE AS + TRUSTWORTHY. OK, *if* ExternalGroupDb is owned by an SQL login that has no permissions granted, except one: AUTHENTICATE in the other database *and* only trusted people are db_owner (or can create user in ExternalGroupDb.)

2) Enable DB-chaining. This can also be a security risk, if there are unrelated databases that also are enabled for chaining, and people with permissions to create objects in the other databases also has access to these two database.

3) Certificate signing. No security risks at all, and what I would use in this case, as it appears to be a one-off.

Then you can of course set up some form of replication of the data to be exposed elsewhere to avoid the permission problems.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Chris Harshman
Chris Harshman
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22754 Visits: 5765
Maxer (12/19/2016)
I could sign the stored procedure with a cert but...that's a PITA....

That's probably the safest option, and when you do it once, it becomes quite easy after that. I regularly use a certificate for this kind of cross database query in a stored proc.
https://www.mssqltips.com/sqlservertip/3336/how-to-use-module-signing-for-sql-server-security/
Maxer
Maxer
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4610 Visits: 1682
Thanks that is most helpful.

Wanted to make sure I didn't miss some simple option or way around it, etc...

Same for VIEWS I assume, and a VIEW can't do Execute As so...
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13372 Visits: 879
Yes, for views database chaining are the only option. However, if you want the interface of a view, you could use a multi-statement function and wrap a view around it.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search