有沒有一種快速的方法來查找 SQL Server 2008 R2 中所有已加密/具有加密數據的列？

有沒有一種快速的方法來查找 SQL Server 2008 R2 中所有已加密/具有加密數據的列？

我需要取消開發伺服器中所有加密列中的數據（根據我們的業務規則）。我知道大多數列，因為我們經常使用它們，但我想徹底，而且我想能夠證明我已經找到了它們。

我搜尋了網路，查看了INFORMATION_SCHEMA並檢查了我認為有用的 DMV 以及sys.columns和**sys.objects——**但到目前為止還沒有運氣。

假設您正在討論使用 SQL Server 密鑰加密的數據，則可以找到這些列。

該Key_name()函式將返回用於加密該特定值的密鑰的名稱，如果沒有使用“已知”密鑰（第 3 方，或簡單未加密）加密任何內容，則返回 NULL。

有了這個知識，我們可以測試每一列，看看它是否至少包含一個行，該行的 varbinary 值返回一個鍵名

key_name() 的功能

--create a test database
CREATE DATABASE [Test_ENCR]
GO

--change context
USE [Test_ENCR]
GO


--because it's possible to encrypt different rows with different keys I'll create 2 keys for this demo
-- Create a symmetric key
CREATE SYMMETRIC KEY symmetricKey1
  WITH ALGORITHM = AES_128
  ENCRYPTION BY PASSWORD = 'password01!';
GO

-- Create a second key
CREATE SYMMETRIC KEY symmetricKey2 
  WITH ALGORITHM = AES_128
  ENCRYPTION BY PASSWORD = 'password02!';
GO



--create a table that will have a column holding:
--1: encrypted row with key1
--2: encrypted row with key2
--3: a non encrypted just varbinary value

CREATE TABLE encryptedTable
(ID int IDENTITY PRIMARY KEY,
EncryptedCol varbinary(256) NOT NULL);
GO


-- open key1
OPEN SYMMETRIC KEY symmetricKey1 
   DECRYPTION BY PASSWORD = 'password01!';
GO

-- open key2
OPEN SYMMETRIC KEY symmetricKey2 
   DECRYPTION BY PASSWORD = 'password02!';
GO

--insert encrypted data with key1
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey1'), 'EncryptedText1'));
GO

--insert encrypted data with key2
INSERT INTO encryptedTable(encryptedCol)
VALUES ( ENCRYPTBYKEY (Key_GUID('symmetricKey2'), 'EncryptedText2'));
GO


--insert just varbinary data
INSERT INTO encryptedTable(encryptedCol)
VALUES (CONVERT(varbinary(256),'NotEncryptedTextJustVarBinary'))



--have a look, without the key, all varbinary for you.
SELECT * FROM encryptedTable
GO

結果：

--Return all key_names
SELECT DISTINCT     key_name(encryptedcol), 
                   EncryptedCol 
FROM encryptedTable;

結果：

如何實現它來查找加密列

--How do we dynamically find all the columns that have at least one row with a encrypted value?

-- first we will find all tables and column with a varbinary datatype
-- then we will test all those columns with a simple select
-- If the key_name() function returns a value, the column and table name are stored together with the keyname

--create a table to hold all varbinary columns and tables
CREATE TABLE #TablesWithVarbinCols (    ID int IDENTITY,
                               TableName nvarchar(128),
                               ColumnName nvarchar(128)
                               );

--create a table to hold the end result
CREATE TABLE #TablesWithEncryption (
                               TableName nvarchar(128),
                               ColumnName nvarchar(128),
                               KeyName varchar(128)
                               );


--find and store all table and column names of user tables containing a varbinary column
INSERT INTO #TablesWithVarbinCols (TableName,ColumnName)
SELECT      o.[name] as TableName,
           c.[name] as ColumnName
FROM        sys.objects o
INNER JOIN  sys.columns c
ON          o.[object_id]=c.[object_id] 
INNER JOIN  sys.types t
ON          c.system_type_id=t.system_type_id
WHERE       o.[type]='U'
AND         t.name=N'varbinary'
AND         c.max_length > -1;


DECLARE @col nvarchar(256)
DECLARE @tab nvarchar(256)
DECLARE @c int = 1
DECLARE @MaxC int
DECLARE @SQL varchar(max)

SELECT @MaxC=MAX(ID)
FROM #TablesWithVarbinCols

--loop the previous result and create a simple select statement with a key_name() is not null where clause. 
--If you have a result, store the details
WHILE @c <= @MaxC
BEGIN
   SELECT  @Tab=TableName,
        @col=ColumnName
   FROM    #TablesWithVarbinCols
   WHERE   ID=@c

   SET @SQL='  INSERT INTO #TablesWithEncryption (TableName, ColumnName, KeyName)
               SELECT DISTINCT '''+@Tab +''',''' +@col +''', key_name('+@Col +') from '+ @tab +' 
               WHERE key_name('+@Col +') is not null;'
   exec (@SQL)

   DELETE
   FROM #TablesWithVarbinCols
   WHERE id=@c;
   SET @c=@c+1
END

--select the result
SELECT * FROM #TablesWithEncryption;

結果：

--cleanup
DROP TABLE #TablesWithVarbinCols;
DROP TABLE #TablesWithEncryption;

引用自：https://dba.stackexchange.com/questions/56844