GDPR – Database Consultant

Here is my work-sheet for safely hiding databases from SSMS that I suspect are unused

-- DetachDB.sql

-- 1. List all attached databases with file paths

	SELECT db_name(database_id) [Database], Physical_Name
	FROM sys.master_files
	order by [Database]

-- 2. Create Attach Script for chosen db (accumulate history here)

	USE [master]; -- on some servername
	CREATE DATABASE xxx ON
	(FILENAME = 'D:\SQLData\xxx.mdf'),
	(FILENAME = 'D:\SQLLogs\xxx.ldf')
	FOR ATTACH;

	USE [master]; -- on some servername
	CREATE DATABASE Test ON
	(FILENAME = 'D:\SQLData\Test.mdf'),
	(FILENAME = 'D:\SQLLogs\Test_log.ldf')
	FOR ATTACH;

-- 3. Detatch Database

	USE [master];
	EXEC MASTER.dbo.sp_detach_db @dbname = N'xxx';

-- 4. To rollback, re-attach database (scripted in step-2)

There is a fab new tool in SSMS 17.5 that helps with the GDPR spadework.

That is, the right of EU citizens to have their personal data deleted on request, enforceable from 25 May 2018.

To start right-click on a database, choose Tasks / Classify Data.

The wizard then searches the current database and attempts to classify table-columns into categories. For example a column called ‘mobile’ containing telephone numbers would be categorized as ‘contact Info’.

Then the wizard adds a sensitivity label (contact-info would be “Confidential – GDPR”)

Its a good idea to look at the actual data in a second screen whilst working down the recommendations list (in the first screen).

For each table-column you can accept / change / delete the recommendation.

Then, when you are done, you can save your work by clicking on “Accept selected recommendations”.

This is then saved within each databases system view called sys.extended_properties.

Be assured – that all selections can be changed / removed indefinitely, and that the tables / columns / data is not directly changed in any way.

The result, is a smart Report which can be printed or emailed out, demonstrating that you have it all under control 😉