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
	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)

Hiding passwords

Here is a worked example of my Obfuscation method.

-- obfuscate_example3.sql


-- read the table

	SELECT *
	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