Post reply

permission across databases

  • 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

     

  • August 30, 2006 at 9:13 am

    #657569

    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.

  • August 30, 2006 at 9:54 am

    #657602

    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

    Greg

  • August 30, 2006 at 11:12 am

    #657643

    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

     

  • August 30, 2006 at 2:46 pm

    #657696

    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

    Greg

  • August 31, 2006 at 6:15 am

    #657767

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

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

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