Granting Select Permissions to another db

Question

Granting Select Permissions to another db

Mark Harley

SSCrazy

Points: 2259
More actions
November 24, 2014 at 12:37 pm

#292732

I have a a few udf's that pull data another database on the same instance. When the application user attempts to access the functions, an error is thrown stating they can't access the other db under the current security context. Is it possible to grant read access to the tables in that db? If not, what's the best way to handle this problem?

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

There are a couple of possibilities. The best is to sign the functions with a certificate which exists in both databases. In the other database, you create user from the certificate which you grant permissions on the required tables.

I have a longer article on my web site where discuss this technique in detail and also discuss other solutions for cross-database access.

http://www.sommarskog.se/grantperm.html

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

Mark Harley SSCrazy Points: 2259 More actions · Answer 2

Thank you for the excellent article! I've attempted to make it work using certificates, but I'm still having a problem. Here's what I've done so far:

1. Created the certificate on the application database, giving the ownership to the application user

2. Signing the functions and the sp that calls them with the certificate

3. Created a certificate with the same name on the other database

4. Created a user from that certificate

5. Granted select permissions to that user on the required tables

After having done all this, I still get the following error:

The server principal "Quota_User" is not able to access the database "CommonObjects" under the current security context.

What have I missed?

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

It sounds as if you have an EXECUTE AS somewhere in your procedures or functions. When you impersonate a database user you are sandboxed into the current database. This is also something I discuss in the article.

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

Mark Harley SSCrazy Points: 2259 More actions · Answer 4

I believe the problem is that I haven't associated the application user with the certificate that was created on the application database. When I run the following, as you suggested in the linked article, there is no listing for "USER MAPPED TO CERTIFICATE":

SELECT SYSTEM_USER, USER, name, type, usage FROM sys.user_token

I considered creating a second user from the certificate and executing the sp as that new user, but I don't think it's possible to impersonate that type of account. Is it possible to map the certificate to the existing user? My searching is failing me on this one.

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

No, that's not the ticket. The certificate user is only there to tie the cert to the permissions. It has nothing to do with the application.

As I said, this error typically indicates that you have an EXECUTE AS somewhere.

Without seeing your code, I cannot say much more.

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

Mark Harley SSCrazy Points: 2259 More actions · Answer 6

I see what you're saying, and I realized my mistake shortly after making that post.

I believe I have it figured out. When I first created the certificates, I made the silly mistake of assuming the certificate name (and not the underlying keys) were what was important, and I didn't backup the original certificate to restore on the second server. I merely created another certificate on the second server that had the same name as the original certificate. It was a stupid mistake.

Anyway, it appears to be working correctly now. Thanks for all the help!