Table-valued UDF on one database selects data from another database on the same SQL instance - how to make this work without assigning explicit user permissions on db_2?

Question

Post reply

Table-valued UDF on one database selects data from another database on the same SQL instance - how to make this work without assigning explicit user permissions on db_2?

mphilippopoulos

SSC Eights!

Points: 846
More actions
January 23, 2012 at 12:13 pm

#255338

I have a table-valued UDF on database db_1 that selects data from a table in another database - db_2 - on the same instance.
Permissions are configured explicitly for users on the UDF.
However, I would like to avoid assigning explicit permissions to users on db_2.
How can I make this work so that when a user executes this UDF (on database db_1), this user implicitly gains access to database db_2 without having been assigned explicit permissions on that database?
I don't want to give blanket access to the guest user on db_2.
Should I perhaps give the guest user SELECT permissions ONLY on that specific table on db_2 that is used in the UDF?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jared SSC Guru Points: 61793 More actions · Answer 1

Is EXECUTE AS not an option?

http://msdn.microsoft.com/en-us/library/ms188354.aspx

Jared
CE - Microsoft

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

SQLKnowItAll (1/23/2012)
Is EXECUTE AS not an option?
http://msdn.microsoft.com/en-us/library/ms188354.aspx

The thought has crossed my mind, but I am a bit short on the specifics.

In fact, I was reading that very page today.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

SQLKnowItAll (1/23/2012)
Is EXECUTE AS not an option?
http://msdn.microsoft.com/en-us/library/ms188354.aspx

It might not be an option if you've written a nice, high performance, inline Table Valued Function.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 4

Jeff Moden (1/23/2012)
SQLKnowItAll (1/23/2012)
Is EXECUTE AS not an option?
http://msdn.microsoft.com/en-us/library/ms188354.aspx
It might not be an option if you've written a nice, high performance, inline Table Valued Function.

Sorry, I don't follow, can you clarify?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jared SSC Guru Points: 61793 More actions · Answer 5

Marios Philippopoulos (1/24/2012)
Jeff Moden (1/23/2012)
SQLKnowItAll (1/23/2012)
Is EXECUTE AS not an option?
http://msdn.microsoft.com/en-us/library/ms188354.aspx
It might not be an option if you've written a nice, high performance, inline Table Valued Function.
Sorry, I don't follow, can you clarify?

From Microsoft:

In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers.

Jared
CE - Microsoft

Jason Wolfkill SSCrazy Eights Points: 9832 More actions · Answer 6

mphilippopoulos (1/23/2012)
I have a table-valued UDF on database db_1 that selects data from a table in another database - db_2 - on the same instance.
Permissions are configured explicitly for users on the UDF.
However, I would like to avoid assigning explicit permissions to users on db_2.
How can I make this work so that when a user executes this UDF (on database db_1), this user implicitly gains access to database db_2 without having been assigned explicit permissions on that database?
I don't want to give blanket access to the guest user on db_2.
Should I perhaps give the guest user SELECT permissions ONLY on that specific table on db_2 that is used in the UDF?

You could grant the user SELECT permission only on the specific table on db_2 that is used in the UDF, but this could be a huge headache if you are managing a large number of users. You may want to consider setting up a database role on db_2 that includes only the necessary select permission.

Another option would be to enable cross-database ownership chaining (if ownership chaining will solve the problem in your context):

http://msdn.microsoft.com/en-us/library/ms188694.aspx

You should thoroughly review the implications of enabling cross-db ownership chaining before doing so!!!

Jason Wolfkill

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

Hmmm... I haven't tried it but I wonder if using a synonyn for the remote table would work in this case.

Also, is the function being used alone? (Probably not). It may be worth the try to "EXECUTE AS OWNER" in the proc calling the function IF the owners of both DB's are "SA".

Again... haven't tried it (don't have the time just now)... just making suggestions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 8

Thank you all for the suggestions, we are considering all these options.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

After reading through this article - http://www.sommarskog.se/grantperm.html#certcrossdb - I think I will go with certificates, specifically a certificate shared in common by the 2 databases that I need to work with, db_1 and db_2.

Here are the steps:

Go to db_2:

(1) Create a self-signed certificate in db_2

(2) Backup the certificate to disk

(3) Create a user from the certificate - say, certuser

(4) Grant SELECT permission on the specific table in db_2, tbl2, to certuser

Go to db_1:

(5) Import the certificate to db_1 from the backed up copy taken in step 2

(6) Add a signature to the UDF using the certificate

At this point any user with rights to execute the UDF on db_1 will implicitly have access on tbl2 in db_2 through the certificate user, certuser. User certuser was created in db_2 (step 3) and was granted SELECT permissions on table tbl2 (step 4).

Based on what is said on that link above, this is probably the best solution for this scenario.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]