June 16, 2003 at 11:28 pm
We have several production databases and one control database. Production databases are accessed through Stored Procedures (owned by dbo) in control database. I want to grant normal users only EXECUTE rights on control database's Stored Procedures. This does not seem to be enough. Every time user with these rights executes SP in Control DB that accesses Production databases SQL Server throws rights violation error about Production database.
What is the right configuration to get this working?
Any ideas?
__
Lauri
June 17, 2003 at 2:44 am
Hi Lauri,
Unfortunately, the users will need the relevent SELECT, INSERT, DELETE, UPDATE permissions in the production databases. This is because the ownership chain has been broken. If the underlying objects referenced in the SP are in the same DB and have the same owner as the SP, then it will work.
Look up Ownership Chains in BOL for a bit more info...
Clive Strong
June 17, 2003 at 3:12 am
Thank you for answering.
Can this be done using linked servers within one SQL Server? Eg. Server linked to itself.
Currently we have one production SQL Server. Production databases and control database reside in this server. In the future we have multiple production SQL Servers, but still one Control DB in one SQL Server. We are planning using linked servers to handle this. One control database server linked to multiple production servers. Do you see any problems in this solution?
__
Lauri
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply