fake id for the existing id

Question

Hi,

I have a table EMP with EMP_id field and wantes to create fake EMP_id filed for each EMP_id like below. Could you please help me how to generate fake EMP_Id column for each EMP_Id

Table EMP:

EMP_Id

1020
10245
10200
50300

and result should be :

EMP_Id, Fake EMP_Id

---

1020 awert12d
10245 adai34kd
10200 dfd23234
50300 ekwekfl23

Answer 1

Hi,

Not clear to me what are the rules regarding the fake EMP_Id which you want to get, but the following points should help you solve your needs probably

(1) If the fake EMP_Id is random value then you can use one of the solutions to build random string which mentioned in following posts

using TSQL: https://social.technet.microsoft.com/wiki/contents/articles/21196.t-sql-random-string.aspx

Using CLR: https://ariely.info/Blog/tabid/83/EntryId/134/SQL-Random-String-using-CLR.aspx

If the fake value is not random then you need to explain what the rules to create the fake fake EMP_Id, and we will try to help you generate the values

let's assume that you finished step one and you have fake EMP_Id values...

(2) If you need to have the same fake EMP_Id for each row always, then you must store that information in the table. This means that you need to add a column for the fake EMP_Id -> generate a fake EMP_Id as explain in point (1) and store it

(3) If you simply need a fake EMP_Id then you can use the string you generate as in point (1) or better solution is to use Dynamic Data Masking

My first guess based on the information we have (which is almost nothing), is that using Dynamic Data Masking g is what you need. This way you can use the Dynamic Data masking function Random: this function on numeric column will return random value for users that does not have the permission to see the real value

Check this Document to learn more about Dynamic Data masking

For example:

CREATE TABLE EMP (EMP_Id INT)  
GO  
  
INSERT EMP(EMP_Id) values (2),(4536),(86),(345)  
GO  
  
ALTER TABLE EMP   
	ALTER COLUMN EMP_Id ADD MASKED WITH (FUNCTION = 'random(1,9999)');   
GO  
  
-- Create new user which will see only a fake values since he have no permission to see the real value  
  
CREATE USER MaskingTestUser WITHOUT LOGIN;  
GO  
GRANT SELECT ON EMP to MaskingTestUser  
GO  
  
-- this is what the user will see (execute multiple times to test it)  
EXECUTE AS USER = 'MaskingTestUser';    
	SELECT * FROM EMP -- random value  
REVERT  

Answer 2

Hi，@Zaran

Welcome to Microsoft T-SQL Q&A Forum!

Please check this：

Create table #temp  
(  
 EMP_Id varchar(100) unique  
)  
Insert into #temp values(1020),(10245),(1200),(50300)  
SELECT EMP_Id,CONVERT(nvarchar(10),LEFT(REPLACE(NEWID(),'-',''),8))AS FakeEMP_Id  
FROM #temp  

Here is result:

Here's a link on random strings that I recommend you read.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.