Masking field in Database table

Today I came across a situation where my manager suddenly asked me to mask a filed in a table. Generally people can do symmetric key encryption and stuff. More info is available at http://msdn.microsoft.com/en-us/library/ms179331.aspx. What if if that's an immediate request and you are not aware of doing encryption. The best and easiest way is using VARBINARY. Take an example

CREATE TABLE Emp
(
    EmpId INT IDENTITY(1,1)
,EmpName VARCHAR(50)
,SSN VARCHAR(15)
,MaskedSSN AS CAST(SSN AS VARBINARY(50))
)

INSERT INTO Emp
SELECT 'ABCD','123-45-3215'
UNION
SELECT 'FERG','879-55-4312'

In the above example I am using a computed column MaskedSSN. This filed will be re-calculated each and every time a query is ran against the table. Now do a SELECT.

SELECT EmpID,EmpName,SSN,MaskedSSN FROM Emp. The output will be


EmpID EmpName SSN MaskedSSN
1 ABCD 123-45-3215 0x3132332D34352D33323135
2 FERG 879-55-4312 0x3837392D35352D34333132

SSN Field is masked now. You can create a view which will pull EmpID,EmpName,MaskedSSN as SSN and take out access to table. If you by default want the actual SSN Field to be masked in the table,  Use the following script


CREATE TABLE Emp
(
    EmpId INT IDENTITY(1,1)
,EmpName VARCHAR(50)
,SSN VARBINARY(256)
)

INSERT INTO Emp
SELECT 'ABCD','123-45-3215'
UNION
SELECT 'FERG','879-55-4312'

When you do a select, the output will be


EmpID EmpName SSN
1 ABCD 0x3132332D34352D33323135
2 FERG 0x3837392D35352D34333132

If you want to find the actual SSN, you can use the following query
SELECT EmpID,EmpName,CAST(SSN AS VARCHAR(256)) AS SSN FROM Emp
which will give the actual SSN.

EmpID EmpName SSN
1 ABCD 123-45-3215
2 FERG 879-55-4312