Calling SQL Guys --loveindia

[SqlHelp45]

I have a requirement where we need to scrub/mask data for personal information like Name/SSN and other details. I need a SP to achieve this task. This will be done whenever we restore production data to test environment so developers will not be able to see actual information. SQL Server used is 2008R2

I have a (primary table) and this table contains columns like Name. We need to scrub data for only this particular column and all other column values should be the same.

We have a second table named Test which contains dummy/fakes names. So I need a SP which will grab one record from Test table and replace it the value in primary table.

Please guys. Help me with this. Manager gaadu torture chestunnadu and this P1 item.

[sanbk]

Idi help avuthadi emo choodu broo 

https://www.mssqltips.com/sqlservertip/3091/masking-personal-identifiable-sql-server-data/

[150bryant]

http://www.sqlservercentral.com/Forums/Topic1475523-391-1.aspx

go through this

[150bryant]

http://www.calsql.com/2009/09/ms-sql-server-data-masking.html

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 

 

--sql server data masking , replace sensitive data with some dummy data
GO
--PR_UPDATE_REFERENCES 'sales.customer','customerid'
GO
--
CREATE procedure PR_UPDATE_REFERENCES(@TBL_NAME VARCHAR(256),@PK_COL_NAME VARCHAR(256),@IDENTITY_COL VARCHAR(256)='IID_TEMP',@MASK_WORD VARCHAR(256)='MASK')
AS
SET NOCOUNT ON
-- Adding the Identity Column

select 'alter table '+ @TBL_NAME +  ' add ' + @IDENTITY_COL +' INT IDENTITY(1,1)'
select 'GO'

-- If the primary key is numeric data type then the mask word will be '' to avoid the casting errors
select @MASK_WORD = '' from sys.columns a join sys.types b on a.system_type_id = b.system_type_id
where object_id = object_id(@TBL_NAME)
and a.name = @PK_COL_NAME
AND b.name in ('tinyint','smallint','int','numeric')

-- updating all the foreighn keys 1st with identity column + 'MASK'
-- will update the Primary key at the end of the script
-- !! update through join with new added identity column 

select distinct 
-- Below part will produce the script to drop  All Constraints 
'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' DROP CONSTRAINT ' + object_name(constid)
from
sys.sysforeignkeys A 
 join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid 
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid 
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID 
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME  and C.NAME = @PK_COL_NAME
---------------------------------------------------------------------------------------------------------
-- Below statement will update all the foreing keys with the values in the updated primary key
select + CHAR(13) + 'UPDATE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' SET ' + B.name 

+ ' = CAST('+object_name(rkeyid) 
+
'.'+ @IDENTITY_COL + ' AS VARCHAR(9))' +' + '''+ @MASK_WORD +'''' +' FROM ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid)+' ' + 
'JOIN '
+ SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) + ' '+ 
+ 'ON ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) +'.'
+C.NAME +' = '
+SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) + '.'+ 
B.NAME
from
sys.sysforeignkeys A 
 join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid 
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid 
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID 
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME  and C.NAME = @PK_COL_NAME

---------------------------------------------------------------------------------------------------------
-- Below statement will update all the foreing keys with the values in the updated primary key

SELECT 'update '+ @TBL_NAME + ' set ' + @PK_COL_NAME + ' = '+@IDENTITY_COL +'+'''+ @MASK_WORD+''''

------------------------------------------------------------------------------------------------
select + CHAR(13) + 
-- Below statement will produce the script to Create All Constraints which are Scripted to Dropped Before
'ALTER TABLE ' + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(fkeyid) +' ADD CONSTRAINT ' + object_name(constid)
+' FOREIGN KEY (' + b.name
+ ') REFERENCES '  + SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid)
+ '(' + c.name + ')'
from
sys.sysforeignkeys A 
 join syscolumns b on a.fkeyid = b.id and a.fkey = b.colid 
join syscolumns c on a.rkeyid = c.id and a.rkey = c.colid 
JOIN sys.objects SOF ON SOF.OBJECT_ID = A.FKEYID 
where
SCHEMA_NAME(SOF.SCHEMA_ID) +'.'+ object_name(rkeyid) = @TBL_NAME  and C.NAME = @PK_COL_NAME
------------------------------------------------------------------------------------------------
-- Dropping the Identity Column
select 'GO'

select 'alter table '+ @TBL_NAME +  ' drop COLUMN ' + @IDENTITY_COL 

[mtkr]

Primary tbl lo n Test tbl lo common col unte use join n update noo..

r if noo mtchng colms.. define a row num n join on it nooo...

 

[SqlHelp45]

Gone through those articles. Work out kaledhu bro.. 2 days unchi try chestunna but not able to get through it. May be I am trying it in a wrong way

[SqlHelp45]

5 minutes ago, mtkr said:

Primary tbl lo n Test tbl lo common col unte use join n update noo..

 

UPDATE DrugChain_1
SET DrugChain_1.DonorName = Test.DonorName
FROM DrugChain_1 AS Table_A INNER JOIN Test AS Table_B
ON Table_A.DonorName = Table_B.DonorName

When I use this statement I am getting a error at Test.DonorName stating  "The multi-part identifier "Test.DonorName" could not be bound."

[SqlHelp45]

Any help guys konchem high priority undhi ee task.

[mtkr]

35 minutes ago, SqlHelp45 said:

UPDATE DrugChain_1
SET DrugChain_1.DonorName = Test.DonorName
FROM DrugChain_1 AS Table_A INNER JOIN Test AS Table_B
ON Table_A.DonorName = Table_B.DonorName

When I use this statement I am getting a error at Test.DonorName stating  "The multi-part identifier "Test.DonorName" could not be bound."

u have already provided alias to tables as Table_A and Table_B... and using table names in Set condition DrugChain_1.DonorName = Test.DonorName...

change tht tabls names to alias names n run it agnn...

 

n as u r updating the tbl.. try to use begin n end tran....

[SqlHelp45]

7 minutes ago, mtkr said:

u have already provided alias to tables as Table_A and Table_B... and using table names in Set condition DrugChain_1.DonorName = Test.DonorName...

change tht tabls names to alias names n run it agnn...

 

n as u r updating the tbl.. try to use begin n end tran....

UPDATE DrugChain_1
SET Table_A.DonorName = Table_B.DonorName
FROM DrugChain_1 AS Table_A INNER JOIN Test AS Table_B
ON (Table_A.DonorName = Table_B.DonorName) .. I am getting same error The multi-part identifier "Table_A.DonorName" could not be bound."

[mtkr]

14 minutes ago, SqlHelp45 said:

UPDATE DrugChain_1
SET Table_A.DonorName = Table_B.DonorName
FROM DrugChain_1 AS Table_A INNER JOIN Test AS Table_B
ON (Table_A.DonorName = Table_B.DonorName) .. I am getting same error The multi-part identifier "Table_A.DonorName" could not be bound."

change   UPDATE DrugChain_1 to Table_A   (wch is alias name)

n use begin tran n end tran for safety...

[SqlHelp45]

24 minutes ago, mtkr said:

change   UPDATE DrugChain_1 to Table_A   (wch is alias name)

n use begin tran n end tran for safety...

When I ran this query it gives 0 rows effected. FYI both tables does not have any relation between them. I am not sure whether this will be any effect.

UPDATE Table_A
SET Table_A.DonorName = Table_B.DonorName
FROM DrugChain_1 AS Table_A INNER JOIN Test AS Table_B
ON (Table_A.DonorName = Table_B.DonorName)

[150bryant]

rendu tables ki relation lekunda how can you join?

[SqlHelp45]

6 minutes ago, 150bryant said:

rendu tables ki relation lekunda how can you join?

yeah vere em anna options unnayi aa bro..

[mtkr]

26 minutes ago, SqlHelp45 said:

When I ran this query it gives 0 rows effected. FYI both tables does not have any relation between them. I am not sure whether this will be any effect.

UPDATE Table_A
SET Table_A.DonorName = Table_B.DonorName
FROM DrugChain_1 AS Table_A INNER JOIN Test AS Table_B
ON (Table_A.DonorName = Table_B.DonorName)

hmmm...