Bewerken

Delen via

sp_refresh_parameter_encryption (Transact-SQL)

  • Artikel

Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance

Updates the Always Encrypted metadata for the parameters of the specified non-schema-bound stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger in the current database.

Transact-SQL syntax conventions

sp_refresh_parameter_encryption
    [ @name = ] N'name'
    [ , [ @namespace = ] { OBJECT | DATABASE_DDL_TRIGGER | SERVER_DDL_TRIGGER } ]
[ ; ]

Arguments

[ @name = ] N'name'

The name of the stored procedure, user-defined function, view, DML trigger, database-level DDL trigger, or server-level DDL trigger. @name is nvarchar(776), with no default. @name can't be a common language runtime (CLR) stored procedure or a CLR function. @name can't be schema-bound. @name can be a multi-part identifier, but can only refer to objects in the current database.

[ @namespace = ] N'namespace'

The class of the specified module. @namespace is nvarchar(20), with a default of OBJECT. When @name is a DDL trigger, @namespace is required. Valid inputs are DATABASE_DDL_TRIGGER and SERVER_DDL_TRIGGER.

Return code values

0 (success) or a nonzero number (failure).

Remarks

The encryption metadata for parameters of a module can become outdated, if:

  • Encryption properties of a column in a table the module references, have been updated. For example, a column was dropped and a new column is added with the same name, but a different encryption type, encryption key, or an encryption algorithm.

  • The module references another module with outdated parameter encryption metadata.

When encryption properties of a table are modified, sp_refresh_parameter_encryption should be run for any modules directly or indirectly referencing the table. This stored procedure can be called on those modules in any order, without requiring the user to first refresh the inner module before moving to its callers.

sp_refresh_parameter_encryption doesn't affect any permissions, extended properties, or SET options that are associated with the object.

To refresh a server-level DDL trigger, execute this stored procedure from the context of any database.

Note

Any signatures that are associated with the object are dropped when you run sp_refresh_parameter_encryption.

Permissions

Requires ALTER permission on the module and REFERENCES permission on any CLR user-defined types and XML schema collections that are referenced by the object.

When the specified module is a database-level DDL trigger, requires ALTER ANY DATABASE DDL TRIGGER permission in the current database.

When the specified module is a server-level DDL trigger, requires CONTROL SERVER permission.

For modules that are defined with the EXECUTE AS clause, IMPERSONATE permission is required on the specified principal. Generally, refreshing an object doesn't change its EXECUTE AS principal, unless the module was defined with EXECUTE AS USER and the user name of the principal now resolves to a different user than it did at the time the module was created.

Examples

The following example creates a table and a procedure referencing the table, configures Always Encrypted, and then demonstrates altering the table and running the sp_refresh_parameter_encryption procedure.

First create the initial table and a stored procedure referencing the table.

CREATE TABLE [Patients] (
    [PatientID] INT IDENTITY(1, 1) NOT NULL,
    [SSN] CHAR(11),
    [FirstName] NVARCHAR(50) NULL,
    [LastName] NVARCHAR(50) NOT NULL,
    [MiddleName] NVARCHAR(50) NULL,
    [StreetAddress] NVARCHAR(50) NOT NULL,
    [City] NVARCHAR(50) NOT NULL,
    [ZipCode] CHAR(5) NOT NULL,
    [State] CHAR(2) NOT NULL,
    [BirthDate] DATE NOT NULL,
    CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ([PatientID] ASC)
);
GO

CREATE PROCEDURE [find_patient] @SSN CHAR(11)
AS
BEGIN
    SELECT * FROM [Patients]
    WHERE SSN = @SSN
END;
GO

Then set up Always Encrypted keys.

CREATE COLUMN MASTER KEY [CMK1]
WITH (
    KEY_STORE_PROVIDER_NAME = N'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = N'CurrentUser/my/A66BB0F6DD70BDFF02B62D0F87E340288E6F9305'
);
GO

CREATE COLUMN ENCRYPTION KEY [CEK1]
WITH VALUES (
    COLUMN_MASTER_KEY = [CMK1],
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x
    016E000001630075007200720065006E00740075007300650072002F006D0079002F006100360036006200620030006600360064006400370030006200640066006600300032006200360032006400300066003800370065003300340030003200380038006500360066003900330030003500CA0D0CEC74ECADD1804CF99137B4BD06BBAB15D7EA74E0C249A779C7768A5B659E0125D24FF827F5EA8CA517A8E197ECA1353BA814C2B0B2E6C8AB36E3AE6A1E972D69C3C573A963ADAB6686CF5D24F95FE43140C4F9AF48FBA7DF2D053F3B4A1F5693A1F905440F8015BDB43AF8A04BE4E045B89876A0097E5FBC4E6A3B9C3C0D278C540E46C53938B8C957B689C4DC095821C465C73117CBA95B758232F9E5B2FCC7950B8CA00AFE374DE42847E3FBC2FDD277035A2DEF529F4B735C20D980073B4965B4542A34723276A1646998FC6E1C40A3FDB6ABCA98EE2B447F114D2AC7FF8C7D51657550EC5C2BABFFE8429B851272086DCED94332CF18FA854C1D545A28B1EF4BE64F8E035175C1650F6FC5C4702ACF99850A4542B3747EAEC0CC726E091B36CE24392D801ECAA684DE344FECE05812D12CD72254A014D42D0EABDA41C89FC4F545E88B4B8781E5FAF40D7199D4842D2BFE904D209728ED4F527CBC169E2904F6E711FF81A8F4C25382A2E778DD2A58552ED031AFFDA9D9D891D98AD82155F93C58202FC24A77F415D4F8EF22419D62E188AC609330CCBD97CEE1AEF8A18B01958833604707FDF03B2B386487CC679D7E352D0B69F9FB002E51BCD814D077E82A09C14E9892C1F8E0C559CFD5FA841CEF647DAB03C8191DC46B772E94D579D8C80FE93C3827C9F0AE04D5325BC73111E07EEEDBE67F1E2A73580085
);
GO

Finally, replace the SSN column with the encrypted column, and run the sp_refresh_parameter_encryption procedure to update the Always Encrypted components.

ALTER TABLE [Patients]
DROP COLUMN [SSN];
GO

ALTER TABLE [Patients]
ADD [SSN] CHAR(11) COLLATE Latin1_General_BIN2 ENCRYPTED
WITH (
    COLUMN_ENCRYPTION_KEY = [CEK1],
    ENCRYPTION_TYPE = Deterministic,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
) NOT NULL;
GO

EXEC sp_refresh_parameter_encryption [find_patient];
GO

Related content