1

A user in database a has execute permission on a stored procedurte in database a, that stored procedure reads table 1 in database b however when the user executes that procedure they get the following message.

The SELECT permission was denied on the object '1', database 'b', schema 'dbo'.

If I give the user db_datareader permission in database b then the procedure works.

I didn't think I needed to do that though. Please can somebody explain how wrong I am!

Improve this question
4
  • 1
    If you don't grant select permission on object '1', database 'b', it won't work as it needs to read that data. So when you grant db_datareader permission in database b it works since it grants select access on all tables and views in database b which is what it wants. To fix this, you can either grant db_datareader permission on database b or you can grant SELECT only on object '1' in database 'b' which will grant read permission only on single object rather that entire DB.
    – SQLPRODDBA
    Commented Mar 3, 2016 at 15:03
  • Thanks SQLPRODDBA - In the past I'd have done exactly what you're saying. Recently though somebody told me that users should only be given permissions to execute stored procedures and those permissions should allow the user to do whatever the procedure does (read, insert or delete)?
    – chris
    Commented Mar 3, 2016 at 15:44
  • 1
    @chris what you were told is true within the same database. And that is due to Ownership Chaining. But databases are really isolated islands within the instance and need to directed to work with each other.
    – Solomon Rutzky
    Commented Mar 3, 2016 at 16:03
  • Thanks Chaps, I've given you both a +1. Sounds like I need to do some reading but basically across databases you're saying chaining does not work.?
    – chris
    Commented Mar 3, 2016 at 16:21

1 Answer 1

Reset to default
1

This is an issue of ownership chaining and how that doesn't automatically translate across databases as most people would expect. The following MSDN page has a good explanation of what is going on in this situation:

Ownership Chains

That article states that in order to get this to work as most would expect, you need to enable "Cross-Database Ownership Chaining". However, this is a security risk, and that article even has a section for "Potential Threats".

The more secure way to extend permissions is by using module signing. Please see my answer on the following Question (here on DBA.SE) for how to remedy this, especially if you don't want to give the user any access to Database B:

stored procedure can select and update tables in other databases - minimal permissions granted

The first time you go through this exercise it might seem terribly complicated, but after a few times it will make enough sense to not seem to bad ;-).

Improve this answer
3
  • Hello Strutzky - is it really this complicated?
    – chris
    Commented Mar 3, 2016 at 15:20
  • @chris please see my updates to the answer. I linked an article which explains the issue. and I get that it seems really complicated. I felt that way the first couple of times I went through this..but it really isn't that bad...
    – Solomon Rutzky
    Commented Mar 3, 2016 at 15:45
  • Sorry Strutzky but I'll just give them read permission before going through the pain.
    – chris
    Commented Mar 3, 2016 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.