Log Ship and create views in the destination has error!

  • Hi expert,

    I am not the SQL Server expert, please be easy.

    We have a dual-home server, home1=Chicago (primary database is sitting here), home2=Washington (planned secondary data in here)

    The daily process is DB1(store raw data around 250GB) => log shipping (in every minutes)=> DW DB in home1

    We are in Washington, so we want to set up the local database in one new server (all servers are running SQL Server 2005 including the new one) to gain performance and easy for quering in Excel. So the new process should be:

    The daily process is DB1(store raw data around 250GB) => log shipping (in every minutes)=> DW DB in home1 => log shippping (in every 3~6 hours) => DW DB2 in home2

    The permissions for the log shipped copy of the database are inherited from the primary database and cannot be changed without recovering the database which breaks log shipping.  This means that home2 accounts could not be grant permissions on the log shipped secondary database as the primary cannot have home2 accounts added to it. 

    Today, the log shipped secondary is in place.  Creating a database with views of the tables in the DW does not work (see error below).  There is no chaining of permissions across databases so the select against the table specified in the view requires permissions on the table.  Anyone have any ideas on a workaround?   If delegation where in place we could use a loop back linked server.  Or will that work if change the security auth mode on both DW servers to mixed and use a SQL account on a loop back linked server. 

    Msg 916, Level 14, State 1, Line 1

    The server principal "REDMOND\patwood" is not able to access the database "DataWarehouseDB_Production" under the current security context.

    PLEASE HELP!

  • We just tried "trustworthy" option which seems to apply to stored procedures NOT VIEWS - we need to access data by VIEWS.  My understanding is that we need to enable users to run their own queries against the log shipped 2nd database  not to be able to just run SPs.  We did a quick test and set the trustworthy option on and still could not select from the table or the 2nd database views.

    An alternative solution others came up with was to grant select permissions on the appropriate tables in the DW to public.  We tried this on a single table and still encountered the permissions issues.

    Anyone have any ideas?

     

  • out of curiousity what are you using the second server for, the one you are logging shipping too?  You said for querying via excel, but you'd log ship every minute...  The problem with logging shipping is that you cannot use the database while logs are being applied to database.  Have you considered transactional replication?

Viewing 3 posts - 1 through 2 (of 2 total)

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