USE master;GO
-- 創(chuàng)建主密鑰CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongMasterKeyPassword!23';
-- 創(chuàng)建證書(shū)CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
-- 使用證書(shū)保護(hù)數(shù)據(jù)庫(kù)加密密鑰USE YourDatabase;GOCREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
-- 開(kāi)啟透明數(shù)據(jù)加密ALTER DATABASE YourDatabaseSET ENCRYPTION ON;GO

USE YourDatabase;GO
-- 創(chuàng)建列主鍵CREATE COLUMN MASTER KEY MyColumnMasterKeyWITH(    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',    KEY_PATH = 'CurrentUser/My/UniqueKeyIdentifier');
-- 創(chuàng)建列加密密鑰CREATE COLUMN ENCRYPTION KEY MyColumnEncryptionKeyWITH VALUES(    COLUMN_MASTER_KEY = MyColumnMasterKey,    ALGORITHM = 'RSA_OAEP',    ENCRYPTED_VALUE = 0x0123456789ABCDEF0123456789ABCDEF);
-- 加密列ALTER TABLE EmployeesADD EncryptedSSN varbinary(256);GO
-- 更新表以包含加密數(shù)據(jù)OPEN SYMMETRIC KEY MySymmetricKeyDECRYPTION BY CERTIFICATE MyServerCert;
UPDATE EmployeesSET EncryptedSSN = EncryptByKey(Key_GUID('MySymmetricKey'), SSN);GO

USE YourDatabase;GO
-- 為SSN列添加屏蔽規(guī)則ALTER TABLE EmployeesALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(1,"XXX-XX-",4)');GO
審計(jì)SQL Server提供了一種強(qiáng)大的審計(jì)功能，可以記錄對(duì)敏感數(shù)據(jù)的訪問(wèn)和更改。示例腳本：設(shè)置數(shù)據(jù)庫(kù)審計(jì)USE master;GO
-- 創(chuàng)建服務(wù)器審計(jì)CREATE SERVER AUDIT MyServerAuditTO FILE ( FILEPATH = 'C:\SQLAudits\' )WITH (ON_FAILURE = CONTINUE);
-- 啟動(dòng)服務(wù)器審計(jì)ALTER SERVER AUDIT MyServerAuditWITH (STATE = ON);
USE YourDatabase;GO
-- 創(chuàng)建數(shù)據(jù)庫(kù)審計(jì)規(guī)范CREATE DATABASE AUDIT SPECIFICATION MyDatabaseAuditFOR SERVER AUDIT MyServerAuditADD (SELECT, INSERT, UPDATE, DELETE ON OBJECT::dbo.Employees BY public);
-- 啟動(dòng)數(shù)據(jù)庫(kù)審計(jì)規(guī)范ALTER DATABASE AUDIT SPECIFICATION MyDatabaseAuditWITH (STATE = ON);GO

CREATE TABLE Employees (EmployeeID INT PRIMARY KEY,Name NVARCHAR(50),Position NVARCHAR(50),Department NVARCHAR(50),SSN NVARCHAR(11));

SELECT * FROM Employees;