Removing unused databases

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
	(FILENAME = 'D:\SQLData\xxx.mdf'),
	(FILENAME = 'D:\SQLLogs\xxx.ldf')

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

-- 3. Detatch Database

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

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

Hiding passwords

Here is a worked example of my Obfuscation method.

-- obfuscate_example3.sql

-- read the table

	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords]
	WHERE ExternalSystem = 'WEB'
--	AND code = 'U4101259' -- marks test account

-- encrypt the PW 

	UPDATE [CMI_ProDB_Live].[dbo].[RecordPasswords]
	SET [Password] = CONVERT(nvarchar(255), CONVERT(varbinary(255), [Password]), 1) 
	WHERE ExternalSystem = 'WEB'
	AND [Password] NOT like '0x[0-9]%' Collate Latin1_General_CS_AI -- not already encrypted
	AND LEN([password]) > 7
	AND [password] NOT LIKE 'Â%' -- exclude pws that start with 'Â' 601543

-- decrypt on the fly

	SELECT CONVERT(nvarchar(255), CONVERT(varbinary(255), [Password], 1)) [Password]
	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords]
	WHERE LEFT([Password], 3) like '0x[0-9]' Collate Latin1_General_CS_AI -- is encrypted
	AND LEN([password]) > 7
	AND code = 'U4101259' -- marks test account (Password2018)

-- decrypt PW permenantly

	UPDATE [CMI_ProDB_Live].[dbo].[RecordPasswords_16may2018]
	SET [Password] = CONVERT(nvarchar(255), CONVERT(varbinary(255), [Password], 1)) 
	WHERE LEFT([Password], 2) = '0x' Collate Latin1_General_CS_AI -- is encrypted
	AND LEN([password]) > 7


-- check there are no differences

	SELECT aa.password, bb.password, *
	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords_16may2018] aa
	JOIN  [CMI_ProDB_Live].[dbo].[RecordPasswords_16may2018B] bb
	ON aa.contactcounter = bb.contactcounter
	AND aa.code = bb.code
	AND aa.externalsystem = bb.externalsystem
	WHERE aa.externalsystem = 'Web'
	and aa.password  bb.password -- 601543 rows

-- manually recover pws

	SELECT 'update [CMI_ProDB_Live].[dbo].[RecordPasswords] SET [Password] = ''' + bb.[password] + ''' WHERE code = ''' + aa.code + ''''
	FROM [CMI_ProDB_Live].[dbo].[RecordPasswords] aa
	JOIN [CMI_ProDB_Live].[dbo].[RecordPasswords_15may2018_b] bb
	ON aa.code = bb.code
	WHERE aa.ExternalSystem = 'WEB'
	AND bb.ExternalSystem = 'WEB'
	AND aa.[password]  bb.[password]
	AND aa.[password] LIKE '0x%'
--	AND bb.password LIKE 'Â%'
	ORDER BY aa.[password] DESC

GDPR Data Mapping

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 😉