Organizations may need to share their database with a development team in order to solve certain issues. This team is usually part of an outsourced firm. It is therefore necessary to remove sensitive data from the database before it can be shared.
This can be done by following the steps described in this document.
Strip Sensitive Data.sql
To strip sensitive data off the database.
1) Create a folder with the name of StripSensitiveDataManual in our example folder path is D:\ StripSensitiveDataManual
2) Open <SVNFolder> where files are downloaded from the SVN repository.
3) Got to CommonFiles folder location in <SVNFolder>\External\StripSensitiveDatabase\
4) Copay files into StripSensitiveDataManual created in step 1
- Strip Sensitive Data.sql (code to strip the data from database)
5) Got to StripSensitiveDataManual folder location in <SVNFolder>\External\StripSensitiveDatabase\
RegisterAssemblyandCreateFunctionforAgentJob.sql (Register Assemblies and create function used by stripping script)
6) Ensure that owner of the database to be stripped is “sa”, using the following steps:
- Navigate to the database in SQL Server Studio, for example, edABC.
- Right-click on database and click on Properties.
- Select Files from the left pane.
- Set Owner as “sa” and click OK button.
Steps to strip sensitive data
1) Open RegisterAssemblyandCreateFunction.sql file in SQL Server Query window and edit the following for the above database:
- Replace '<DatabaseName>' text with above database name i.e. edABC.
- Replace '<DllFolderPath>' text with the complete folder path where the files in step 1 of the Pre-Requirements are located, for example D:\StripSensitiveDataManual.
2) Execute the above script.
3) After successful execution of above script, open the script Strip Sensitive Data.sql file in SQL Server Query window and execute the script on the same database.