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 

57 comments:

Anonymous said...

solved my purpose....
thanks

Arthur said...

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

calsql said...

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

Terry said...

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

Reneesh Prabha said...

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

chandra sekhar said...

thanks prabha. i have updated the link with the right url.

Anonymous said...

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?

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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.

Anonymous said...

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.

Anonymous said...

Another good thing about playing on the internet is that you can play without money if
you choose. If you're a new entrant in online flash games then online Blackjack would be a unique
game for you and you will try your hands on it. It could be the most straightforward details
that really must be checked and double checked to insure the
guest has an experience that is just not just pleasing, but most importantly,
extraordinary.

Anonymous said...

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!

Anonymous said...

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.

Anonymous said...

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!

Anonymous said...

You may also add forum sections where people can leave their comments, questions, suggestions, and reactions.
The games incorporate different racing tracks, selection of surfaces and numerous
tournaments like the rewards. The following paragraph will explain much about some advices and tricks for the ones from serious
bettors.

Anonymous said...

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.

Anonymous said...

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.

Anonymous said...

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.

Anonymous said...

Right now it looks like BlogEngine is the top blogging platform out there right now.

(from what I've read) Is that what you are using on your blog?

Anonymous said...

Even following the tournament was extended until 4AM
Friday morning, the champion couldn't be decided.
Sportwetten bonus may be the profit that online bookies desire to tell their members.
You can also check if your mobile is compatible with all
the games by clicking the "Compatible Devices" tab for the website.

Anonymous said...

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.

Anonymous said...

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!!

Anonymous said...

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

Anonymous said...

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 .

Anonymous said...

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!

Anonymous said...

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.

Anonymous said...

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!

Anonymous said...

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

Anonymous said...

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.

Anonymous said...

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!

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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.

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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!

Anonymous said...

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

Anonymous said...

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!

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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.

Anonymous said...

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

Anonymous said...

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

Anonymous said...

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.

Anonymous said...

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.

Anonymous said...

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.

Anonymous said...

Aw, this was a really good post. Taking the time and actual
effort to generate a great article… but what can I say… I procrastinate a lot and
never seem to get anything done.

Anonymous said...

Unover the very best Tijnuana hotel and motel rooms.

Anonymous said...

Que tal! mu buena nota, parece que don yahoo me trajo exactamente a la información que
estaba buscando, me encanta Tailandia y espero ir el próximo mes,
me suscribo a esperar las nuevaspublicaciones, gracias Buena nota

Anonymous said...

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

Anonymous said...

Terrific article! That is the kind of info that are meant to
be shared across the internet. Disgrace on the search
engines for no longer positioning this put up upper! Come on over and talk over with my website .
Thanks =)

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