permission across databases

Question

permission across databases

scott clemons

Mr or Mrs. 500

Points: 550
More actions
August 29, 2006 at 2:43 pm

#94358

I have a list of customers in one database (InControl) that I want to access from a second database (InControl_QC). Both databases are owned by the same dbo. I am using the following stored procedure within InControl_QC to access the customer table.
CREATE PROCEDURE [dbo].[IMP_CUST_LKU_proc_Customers_Active]
AS
SELECT
CustomerID,
CustomerName
FROM InControl.dbo.IMP_CUST_tbl_Information
WHERE InActive = 0
ORDER BY CustomerName
Both databases have the same Users and Roles.
I get this error:
"SELECT permission denied on object "IMP_CUST_tbl_Information, database InControl, owner dbo"
I don't want to grant any permission on a User level. All premissions are granted to Roles. Roles are granted premission to EXEC stored procedures that access the tables. There are no permissions granted directly against the tables.
How would I set-up premissions to allow this query activity between databases?
Thanks

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

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

Kemp Davis Ten Centuries Points: 1067 More actions · Answer 1

Create a view of InControl.dbo.IMP_CUST_tbl_Information within the database InControl. Grant SELECT to the Roles. Have your stored procedure in InControl_QC select from the view.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 2

Scott,

Enable cross-database ownership chaining between the two databases. It's off by default after SQL Server installation.

To enable: open properties of the database in Enterprise Manager, select 'Options' tab, check 'Allow cross-database ownership chaining', and OK.

Greg

scott clemons Mr or Mrs. 500 Points: 550 More actions · Answer 3

Greg and Kemp

Thanks for the replies. I have it working by using the VIEW suggestion made by Kemp. It adds more thought to the User/Roles part of the databases, but does keep me from allowing direct permissions on the underlying tables.

I have been doing some reading on the cross-database ownership chaining. There are a few security concerns with it. Have you experienced any problems with this approach?

Thank you both

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

We haven't had any problems. We mitigate the security concerns by having everything owned by an administrative login. Users don't own any objects and are not members of the db_owner or db_ddladmin roles. Application logins have no object permissions, only EXECUTE permission for stored procedures.

Allowing ownership chaining lets us have a "common routine" database that has tables and UDFs that can be used by stored procedures in any database rather than having to maintain copies of the objects in each database that needs them.

Greg

scott clemons Mr or Mrs. 500 Points: 550 More actions · Answer 5

Thanks for the explanation. I like the approach and will look into it.