scramble ssn with sql server

Hi,
I'm trying to scramble the ssn#s within our database. I would need a 9
digit number to be converted into another 9 digit number in our dev
database.
Example #1:
ssn: 123456789 converts to 987654321
Also there is a catch, there is a possibility that there could be
duplicate ssn within a table due to bad data. I was the 2 records with
the same actual ssn# to be converted into the same scrambled ssn# using
sql server (so that the scrambled ssn#s match) for this issue.
Is there a way to do this?
Thanks
:DHi
If you wish to make the data anonymous then you will need an algorithm that
is not easily breakable. Normal encryption procedures would not necessarily
give you the format you want, but just moving numbers or replacement may not
give you the require level of obfuscation.
To change multiple values of a given column you can add an extra column say
hasbeenchange which you set then the data has been changed. You can then
cursor through the records in the table and change each value that does not
already have hasbeenchange set
DECLARE @.ssn char(9)
DECLARE ssn_cursor CURSOR FOR
SELECT ssn
FROM Mytable WHERE hasbeenchange = 0
ORDER BY SSN
OPEN ssn_cursor
FETCH NEXT FROM ssn_cursor INTO @.ssn
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE mytable
SET ssn = fn_jiggerypokery(ssn)
WHERE ssn = @.ssn
AND hasbeenchange = 0
FETCH NEXT FROM ssn_cursor INTO @.ssn
END
CLOSE ssn_cursor
DEALLOCATE ssn_cursor
John
"dmalhotr2001@.yahoo.com" wrote:
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
>|||dmalhotr2001@.yahoo.com wrote:
> Hi,
> I'm trying to scramble the ssn#s within our database. I would need a 9
> digit number to be converted into another 9 digit number in our dev
> database.
> Example #1:
> ssn: 123456789 converts to 987654321
>
> Also there is a catch, there is a possibility that there could be
> duplicate ssn within a table due to bad data. I was the 2 records with
> the same actual ssn# to be converted into the same scrambled ssn# using
> sql server (so that the scrambled ssn#s match) for this issue.
> Is there a way to do this?
> Thanks
> :D
For what purpose do you want to do this? One reason might be that you
want to generate some test data without compromising any confidential
information. In that case you could use the CHECKSUM or RAND functions
to generate some arbitrary values.
Another reason might be that you want to secure the data. "Scrambling"
a number has nothing to do with any meaningful kind of security so here
are some other suggestions instead. You can encrypt the data and/or to
limit access to column/rows using the SQL Server security model.
Probably you'll want to use a combination of both.
SQL Server 2005 has encryption functions built-in, so take a look at
the encryption topics in Books Online. In earlier versions you can use
third party software to achieve the same thing.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||John,
Can I get a copy of dbo.fn_jiggerypokery()?
With a name like that, it 'sounds' interesting...
;-)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:E64B9234-AACC-4453-998E-21ACCFA81699@.microsoft.com...
> Hi
> If you wish to make the data anonymous then you will need an algorithm
> that
> is not easily breakable. Normal encryption procedures would not
> necessarily
> give you the format you want, but just moving numbers or replacement may
> not
> give you the require level of obfuscation.
> To change multiple values of a given column you can add an extra column
> say
> hasbeenchange which you set then the data has been changed. You can then
> cursor through the records in the table and change each value that does
> not
> already have hasbeenchange set
> DECLARE @.ssn char(9)
> DECLARE ssn_cursor CURSOR FOR
> SELECT ssn
> FROM Mytable WHERE hasbeenchange = 0
> ORDER BY SSN
> OPEN ssn_cursor
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> UPDATE mytable
> SET ssn = fn_jiggerypokery(ssn)
> WHERE ssn = @.ssn
> AND hasbeenchange = 0
> FETCH NEXT FROM ssn_cursor INTO @.ssn
> END
> CLOSE ssn_cursor
> DEALLOCATE ssn_cursor
> John
> "dmalhotr2001@.yahoo.com" wrote:
>> Hi,
>> I'm trying to scramble the ssn#s within our database. I would need a 9
>> digit number to be converted into another 9 digit number in our dev
>> database.
>> Example #1:
>> ssn: 123456789 converts to 987654321
>>
>> Also there is a catch, there is a possibility that there could be
>> duplicate ssn within a table due to bad data. I was the 2 records with
>> the same actual ssn# to be converted into the same scrambled ssn# using
>> sql server (so that the scrambled ssn#s match) for this issue.
>> Is there a way to do this?
>> Thanks
>> :D
>>