Post reply

Multi-Database View in SQL Server 2000 - Restricting Permissions

  • October 26, 2011 at 2:51 pm

    #246949

    Our payroll company stores detailed payroll for us in a SQL Server 2000 database (there's no chance we can upgrade it). We have two different locations, and it stores each in a different database.

    I have created a third database, and in it I have a set of views that union the collective payroll data and aggregate it. I had no problems setting any of this up, or accessing any of the views, because I have permission to all the underlying data...

    Now I want to give access to other users to my highly aggregated views ONLY. The views have no problems accessing a view dependancy in the same database, but they fail when trying to access tables in the other database: "SELECT permission denied on object 'tbl_co_state_tax', database 'adc13992', owner 'dbo'." All tables and views in question are owned by dbo.

    Is there a way around this? I absolutely do not want to grant access to raw payroll data. That is out of the question. My boss does not want me to develop anything inside the payroll databases; he's asked that I restrict all custom objects to another database. Is there anything I can do here?

    Thanks

    --Jamin VanderBerg

  • October 26, 2011 at 3:02 pm

    #1399923

    When going across databasses, the users have to be given access to the base tables in the other databases. The only way around this is to pull the data into your third database and build the view over those tables.

  • October 26, 2011 at 3:16 pm

    #1399930

    In SQL 2000 I agree with Lynn, there is really nothing you can do but copy the data to a single database. In SQL 2005 you could probably build out a signed sproc that could return the data, have never seen a signed view discussed..

    CEWII

  • October 26, 2011 at 3:32 pm

    #1399936

    Wouldn't a distributed partitioned view help achieve that?

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • October 26, 2011 at 4:04 pm

    #1399948

    No because you still have to overcome the database level security. And I don't think that it can be overcome with database chaining either.

    CEWII

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

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