Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Avoid any changes to base tables in Datamart

Beantwortet Avoid any changes to base tables in Datamart

  • Friday, January 18, 2013 5:52 PM
     
     
    Sign In to Vote
    0

    Hi,

    We have follwoing flow of data: production database tables TO Datamart Base Tables TO DataMart CDC tables to DB2 mars (final stage where reporting is done).

    Now the business users want to lock down or avoid any changes to prod db tables (as they are replicated to next stage).

    How can we avoid these changes to base tables? I know one option can be SCHEMA BINDING viwes on top of replicated tables. Can you please suggest other options?

    Thanks,

    hsbal

     

All Replies

  • Friday, January 18, 2013 6:10 PM
     
     Answered
    Sign In to Vote
    0

    Hello Harry,

    You can us a DDL Triggers to prevent changes on objects like tables.

    Olaf Helper

    Blog Xing

     
  • Friday, January 18, 2013 7:04 PM
     
     Answered
    Sign In to Vote
    0

    use the Minimum Required Permissions principle.

    1) Deny structure changes to all users (except on the dev platform!!)

    2) Grant structure change permissions to the users that promote the databases from dev to prod to datamart...  you might create a login just for this purpose and disable it unless a promoting a db.

     
  • Friday, January 25, 2013 4:41 PM
    Moderator
     
     
    Sign In to Vote
    0

    Any progress?

    Take table change permission away from all except the DBA.

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012