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!

  • 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 Mar 3 '16 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 Mar 3 '16 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 Mar 3 '16 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 Mar 3 '16 at 16:21
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 ;-).

  • Hello Strutzky - is it really this complicated? – chris Mar 3 '16 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 Mar 3 '16 at 15:45
  • Sorry Strutzky but I'll just give them read permission before going through the pain. – chris Mar 3 '16 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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