Monday, October 12, 2009

ms sql server data masking


SQL Server Data Masking :
Download code
DataMasking.sql

Masking data can be done through updating the confidential information in the database like SSN , Password,Username etc with some other values.
To mask the entire data quickly , its good to create table with list of the tables and columns which you need mask and run the generic statements to generate the update scripts on the table.
But updating the primary key value is difficult task as we need to update the foreign key references as well.
Below procedure will update the primary key and all the related foreign keys.

Note: this procedure will not mask the table references where the PK is identity 
Mostly we don't need to mask the identity values as all are auto generated values.

Steps involved in the below procedure : 
  1. Add an identity column to the primary key table
  2. check the primary key datatype if its character datatype then it will append 'MASK' to the value its going to update
  3. drop all the foreign keys
  4. update all the foreign keys with the primary key table newly added identity columns value while referring the primary key value in the primary key table
  5. update the primary key value with the newly added identity column
  6. adds all the foreign keys which are dropped before in step:3
  7. drop the newly added identity column in the primary key table which is added in step :1 

52 comments:

  1. solved my purpose....
    thanks

    ReplyDelete
  2. Also can be done through SSIS as in http://geekswithblogs.net/Compudicted/archive/2012/03/01/creating-a-custom-ssis-data-flow-component---an-example.aspx

    ReplyDelete
    Replies
    1. Thanks Arthur. I am still working on this to develop a module with sql scripts.

      Delete
  3. Hi, I just wanted to say that you can use DataVeil Freeware to mask SQL Server databases. It takes care of foreign keys automatically. It's very user-friendly and has nice visualizations. The Freeware is fully featured (nothing crippled, no nag screens, etc!), it never expires. The only limit is a maximum of 500,000 masked values per project. You can download and see a short demo video at www.dataveil.com

    ReplyDelete
  4. DataMasking.sql seems to be not available in the given link

    ReplyDelete
    Replies
    1. thanks prabha. i have updated the link with the right url.

      Delete
  5. I am really enjoying the theme/design of your web site.
    Do you ever run into any browser compatibility issues?
    A number of my blog audience have complained about
    my website not operating correctly in Explorer but looks great in Chrome.
    Do you have any recommendations to help fix this problem?

    ReplyDelete
  6. Nice post. I learn something totally new and challenging on sites
    I stumbleupon everyday. It's always helpful to read articles
    from other writers and practice something from their web sites.

    ReplyDelete
  7. Superb, what a web site it is! This web site presents helpful information to
    us, keep it up.

    ReplyDelete
  8. I loved as much as you will receive carried out right here.
    The sketch is attractive, your authored subject matter stylish.
    nonetheless, you command get got an impatience over that you wish be
    delivering the following. unwell unquestionably come further formerly
    again as exactly the same nearly a lot often inside case you shield this hike.

    ReplyDelete
  9. Nice post. I learn something totally new and challenging on sites I stumbleupon on a daily basis.

    It's always helpful to read through content from other writers and
    use a little something from their websites.

    ReplyDelete
  10. Hi there! I know this is kinda off topic however I'd
    figured I'd ask. Would you be interested in exchanging links or maybe guest
    writing a blog article or vice-versa? My site discusses a lot of the same topics as yours and I think we could
    greatly benefit from each other. If you might be interested feel free to shoot me an e-mail.
    I look forward to hearing from you! Excellent blog by the way!

    ReplyDelete
  11. For the reason that the admin of this web page is working,
    no uncertainty very shortly it will be renowned, due to its feature contents.

    ReplyDelete
  12. excellent post, very informative. I wonder why the opposite experts of this sector don't notice this.
    You must continue your writing. I am sure, you
    have a great readers' base already!

    ReplyDelete
  13. In many ways, this can be regarded as something crucial that you would like to take a look at before heading to another step.

    Since the sport has massive followers worldwide it is not
    surprising that lots of searches about roulette strategies and tips are whipped out.

    From blackjack to baccarat strategy, everyone has a fail-proof
    approach to beat the casino games.

    ReplyDelete
  14. So there exists clearly no great virtual air travel ticket price benefit as a result
    of virtual airline deregulation. Playing free
    addicting games or any game accessible on the net can actually provide enjoyment on the household.
    "I 'm sorry drag you to increase your travel, I am developing a knockout paintings.

    ReplyDelete
  15. I have been exploring for a little for any high quality articles or blog posts
    in this sort of house . Exploring in Yahoo I finally stumbled upon this web site.
    Reading this information So i am happy to show that I have
    an incredibly good uncanny feeling I found out exactly
    what I needed. I so much unquestionably will make sure to don?t overlook this website and
    provides it a look regularly.

    ReplyDelete
  16. We refer instead, to the online with free streaming competitions that are conducted on the web from the hundreds every day.
    Something which I realized in the beginning occurs when you do not possess a graphic to suit your needs business, your
    decisions are form of hit and miss, as well as your path
    for your company is like guesswork. For example, in order to discover
    how to get three stars on Angry Birds Ham'o'ween level 1-8, You - Tube user Angry - Bird - Nest provides tutorial shown below.

    ReplyDelete
  17. A motivating discussion is definitely worth comment.
    I think that you ought to publish more on this subject matter, it might not be a taboo subject but
    usually people don't discuss these issues. To the next!

    All the best!!

    ReplyDelete
  18. I visited several websites except the audio feature for audio songs current at
    this web page is genuinely wonderful.

    ReplyDelete
  19. Hi my friend! I want to say that this article is awesome,
    great written and include almost all significant infos.
    I'd like to look extra posts like this .

    ReplyDelete
  20. You really make it seem so easy with your presentation but I find this topic to be actually something that I think I would never understand.
    It seems too complicated and extremely broad for me.
    I am looking forward for your next post, I will try to get the hang of it!

    ReplyDelete
  21. Excellent post. Keep posting such kind of information on your
    site. Im really impressed by your blog.
    Hello there, You've done a great job. I'll definitely
    digg it and in my view recommend to my friends. I am sure they'll be benefited from this site.

    ReplyDelete
  22. Please let me know if you're looking for a author for your site.
    You have some really great posts and I feel I would be a good asset.
    If you ever want to take some of the load off, I'd love to
    write some material for your blog in exchange for a link back to mine.
    Please blast me an email if interested. Regards!

    ReplyDelete
  23. I was able to find good information from your blog articles.

    ReplyDelete
  24. Free slots would be the most popular online gambling possibilities open and give
    a real possibility of winning huge sums of money.
    Games and gambling are 2 of the most popular uses with the Internet
    for casual users. Who would believe even slots can now
    be played through internet.

    ReplyDelete
  25. Hi! I know this is kind of off topic but I was
    wondering if you knew where I could find a captcha plugin for my comment form?
    I'm using the same blog platform as yours and I'm having difficulty finding one?

    Thanks a lot!

    ReplyDelete
  26. Muy buenas! me ha agradado esta nota, creo haber tomadobuena anotacion, amo Tailandia
    y espero regresar en breve me quedo husmeando otras mas,
    me sumo a esperar las novedades, muchas muchas gracias

    ReplyDelete
  27. Hola muy linda nota, parece que don bing me trajo directamente a la información que estaba buscando, me encanta Tailandia y deseo volver el próximo mes, me apunto a leer las nuevasnotas, muchas gracias
    Buen trabajo

    ReplyDelete
  28. Attractive section of content. I just stumbled upon your web site and in accession capital to assert
    that I get actually enjoyed account your blog posts.

    Any way I will be subscribing to your feeds and even I achievement you access consistently rapidly.

    ReplyDelete
  29. Simply desire to say your article is as astonishing.
    The clarity in your post is just spectacular and
    i can assume you are an expert on this subject.
    Well with your permission allow me to grab your RSS feed to keep updated with forthcoming post.
    Thanks a million and please carry on the rewarding
    work.

    ReplyDelete
  30. Hi there just wanted to give you a quick heads up. The words in your article seem
    to be running off the screen in Opera. I'm not sure if this is a formatting issue or something to do with browser compatibility but I
    figured I'd post to let you know. The layout look great though!
    Hope you get the issue resolved soon. Kudos

    ReplyDelete
  31. Thanks for a marvelous posting! I seriously enjoyed reading it,
    you are a great author.I will ensure that I bookmark
    your blog and definitely will come back sometime
    soon. I want to encourage one to continue your great job, have a nice afternoon!

    ReplyDelete
  32. C'est le début de la prédication publique de l'Islam.

    ReplyDelete
  33. What i don't realize is if truth be told how you are now not really
    a lot more well-appreciated than you may be right now.
    You're so intelligent. You realize thus significantly when it comes to this matter, produced me
    in my view consider it from so many varied angles.

    Its like men and women are not interested until it's one thing to accomplish with Woman gaga!
    Your individual stuffs nice. Always care for it up!

    ReplyDelete
  34. I love your blog.. very nice colors & theme. Did you make this website yourself or did you hire someone to
    do it for you? Plz reply as I'm looking to create my own blog and would like to know where u
    got this from. appreciate it

    ReplyDelete
  35. Hola me ha molado esta nota, creo haber tomadobuena nota, amo Tailandia y espero volver el
    próximo mes me quedo chequeando alguna mas, me sumo a esperar las novedades, muchas muchas gracias

    ReplyDelete
  36. Great post. I was checking continuously this blog and I am impressed!
    Extremely useful information specially the last part :) I care for such info much.
    I was looking for this particular information for a long time.
    Thank you and best of luck.

    ReplyDelete
  37. Now I аm goіng away to do my breakfast, once having my breakfast coming yet again to read adԀitional news.

    ReplyDelete
  38. Thank you so much pertaining to giving me an update on this theme on your web page.
    Please know that if a fresh post becomes available or in case
    any modifications occur about the current post, I would
    be thinking about reading a lot more and learning how to make
    good use of those tactics you reveal. Thanks for your efforts and consideration of other people by making this site available.

    ReplyDelete
  39. This excellent website really has all of the info I needed concerning this subject and didn't know who to ask.

    ReplyDelete
  40. Regards for all your efforts that you have put in this.
    Very interesting info.

    ReplyDelete
  41. Me ha interesado , la noticia, realmente excelente,
    gracias por la advertencia, muy ilustrativa. Continuo fisgoneando por la web a
    enterarme de mas noticias informativas, muchas gracias de nuevo.

    ReplyDelete
  42. This piece of writing provides clear idea in support of
    the new users of blogging, that actually how to do blogging and site-building.

    ReplyDelete
  43. Great delivery. Solid arguments. Keep up the great effort.

    ReplyDelete
  44. I am not sure where you're getting your info, but great topic.
    I needs to spend some time learning more or
    understanding more. Thanks for excellent information I
    was looking for this information for my mission.

    ReplyDelete
  45. The internet is mսch more like a little nation, wіth various
    sects and tߋwns liking different things. Unless you are spending money on advertising yoursеlf, the only
    method to generate foot traffic is with attractiѵe content.
    Tһe easiest ԝay is usually to only send the emɑils to usеrs
    who have requested inclusion while onn ɑn email mailing list,
    usually known as opt-in lists.

    ReplyDelete
  46. Hi just wanted to give you a brief heads up and
    let you know a few of the pictures aren't loading properly.
    I'm not sure why but I think its a linking issue.
    I've tried it in two different internet browsers and both show the same outcome.

    ReplyDelete
  47. This comment has been removed by a blog administrator.

    ReplyDelete
  48. This comment has been removed by a blog administrator.

    ReplyDelete
  49. It's hard to come by knowledgeable people in this particular topic, but you seem like you know what you're talking about!
    Thanks

    ReplyDelete
  50. This comment has been removed by a blog administrator.

    ReplyDelete

Featured Post

SQL Server AWS Migration BCP

stored procedure to generate BCP scritps to migrate the SQL Server database. Developed this stored procedure on my labs to simulate t...

Contributors