แก้ไข

KEY_NAME (Transact-SQL)

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Returns the name of the symmetric key from either a symmetric key GUID or cipher text.

Transact-SQL syntax conventions

Syntax

KEY_NAME ( ciphertext | key_guid )

Arguments

ciphertext
Is the text encrypted by the symmetric key. cyphertext is type varbinary(8000).

key_guid
Is the GUID of the symmetric key. key_guid is type uniqueidentifier.

Returned Types

varchar(128)

Permissions

Beginning in SQL Server 2005 (9.x), the visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.

Examples

A. Displaying the name of a symmetric key using the key_guid

The master database contains a symmetric key named ##MS_ServiceMasterKey##. The following example gets the GUID of that key from the sys.symmetric_keys dynamic management view, assigns it to a variable, and then passes that variable to the KEY_NAME function to demonstrate how to return the name that corresponds to the GUID.

Note

In Azure SQL Database and SQL database in Microsoft Fabric, the USE statement isn't available. Instead, connect directly to the master database and run the rest of the code sample.

USE master;  
GO  
DECLARE @guid uniqueidentifier ;  
SELECT @guid = key_guid FROM sys.symmetric_keys  
WHERE name = '##MS_ServiceMasterKey##' ;  
-- Demonstration of passing a GUID to KEY_NAME to receive a name  
SELECT KEY_NAME(@guid) AS [Name of Key];

B. Displaying the name of a symmetric key using the cipher text

The following example demonstrates the entire process of creating a symmetric key and populating data into a table. The example then shows how KEY_NAME returns the name of the key when passed the encrypted text.

-- Create a symmetric key  
CREATE SYMMETRIC KEY TestSymKey   
   WITH ALGORITHM = AES_128,  
   KEY_SOURCE = 'The square of the hypotenuse is equal to the sum of the squares of the sides',  
   IDENTITY_VALUE = 'Pythagoras'  
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y' ;  
GO  
-- Create a table for the demonstration  
CREATE TABLE DemoKey  
(IDCol INT IDENTITY PRIMARY KEY,  
SecretCol VARBINARY(256) NOT NULL)  
GO  
-- Open the symmetric key if not already open  
OPEN SYMMETRIC KEY TestSymKey   
    DECRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y';  
GO  
-- Insert a row into the DemoKey table  
DECLARE @key_GUID uniqueidentifier;  
SELECT @key_GUID = key_guid FROM sys.symmetric_keys  
WHERE name LIKE 'TestSymKey' ;  
INSERT INTO DemoKey(SecretCol)  
VALUES ( ENCRYPTBYKEY (@key_GUID, 'EncryptedText'))  
GO  
-- Verify the DemoKey data  
SELECT * FROM DemoKey;  
GO  
-- Decrypt the data  
DECLARE @ciphertext VARBINARY(256);  
SELECT @ciphertext = SecretCol  
FROM DemoKey WHERE IDCol = 1 ;  
SELECT CAST (  
DECRYPTBYKEY( @ciphertext)  
AS VARCHAR(100) ) AS SecretText ;  
-- Use KEY_NAME to view the name of the key  
SELECT KEY_NAME(@ciphertext) AS [Name of Key] ;

Related content

  • อัปเดตล่าสุดเมื่อ