Post reply

database security across databases

  • May 12, 2008 at 2:43 am

    #121422

    Hello,

    I dont think this is possible, but thought I better check. Can a database role in 1 database be given rights to access another? Using SQL 2000

    Basically a query is being run from an application which queries objects in 2 databases. I have enabled cross database chaining but the owner is different for each object:

    i.e.

    Database 1

    table:

    MEM.Customer

    Database 2

    Table:

    dbo.OrderHDs

    From my understanding both objects need to be owned by the same user/schema for database chaining to work and it may not be possible to change the owner as the application uses these in other areas and would be a big change.

    If there is another way to do this, I would apprieciate your thoughts

    thanks in advance

  • May 12, 2008 at 7:00 am

    #813760

    You will need to give the logins access to both databases.

  • May 12, 2008 at 9:03 am

    #813880

    Correct in that logins really need to be in both databases.

    What I'd suggest is that you setup security in the databases separately as needed and then be sure when you add someone to a role in Db1, you add them in 2. It makes for auditing easier as well.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 12, 2008 at 9:04 am

    #813882

    Create a similar role in the second database. Add logins who are members of the role in the first database to this new role in the second database. Give this new role appropriate permissions.

    Regards,Yelena Varsha

  • May 12, 2008 at 9:05 am

    #813885

    You're correct in your interpretation of cross-database ownership chaining. The objects in both databases must have the same owner unless you grant the applicaton user SELECT permission on the table in Database 2.

    Greg

  • May 13, 2008 at 5:40 pm

    #814878

    Here is a microsoft article on the topic Related to SP3 and SQL 2000:

    Brian RL: Be serious about your work, but do not take yourself too seriously {Philip A. Fisher}

  • May 14, 2008 at 7:01 am

    #815133

    Thanks everyone.

    The application creates the server level logins and assigns permissions on the specific role. Unfortunatly, we cant interact with that.

    Can an 'Execute As ' option be put in a view? I will Just thinking would this be an option?

    Thanks again.

  • May 14, 2008 at 8:16 am

    #815235

    No, but you may be able to handle the additional permissions assignment with a DDL trigger.

  • May 17, 2008 at 10:29 am

    #816878

    I have done same thing.

    DATABASEA UserX View select x from DATABASEB.TABLEB filtered by x

    DATABASEB UserX

    All works the problem is when i gave the linked server information to another part of company they can put in the linked server and catalog DATABASEA and use the view i created.

    However if the user sets up the linked server and leaves the catalog blank - he now can do select x fom DATABASEB.TABLEB and see all the data.

    Any way to prevent this............

Viewing 9 posts - 1 through 8 (of 8 total)

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