SQL Server.提升签名过程权限以从服务器状态读取客户端 IP

Question

另一个从触发器捕获客户端 IP 的请求...例如，当客户端更新表时。在我们的例子中，数据库不受TRUTHWORTHY选项的约束。客户端软是旧的，巨大的和丑陋的，没有办法从其所有数据库调用发送IP。我尝试了一些复制粘贴的编译，但没有成功......如下：

-- Create a test login and test database
CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
CREATE DATABASE certtest
go
-- Move to the test database.
USE certtest
go
-- Create the test user.
CREATE USER testuser
go
CREATE PROCEDURE example_sp AS
   SELECT  client_net_address as client_ip  FROM  sys.dm_exec_connections   WHERE   session_id = @@SPID
go
GRANT EXECUTE ON example_sp TO public
go
-- Create the certificate.
CREATE CERTIFICATE examplecert
   ENCRYPTION BY PASSWORD = 'All you need is love'
   WITH SUBJECT = 'Certificate for example_sp',
   START_DATE = '20020101', EXPIRY_DATE = '20200101'
go
-- transfer certificate to master
BACKUP CERTIFICATE [examplecert] TO FILE = 'C:TEMPexamplecert.CER';
GO
USE [master]
GO
CREATE CERTIFICATE [examplecert2] FROM FILE = 'C:TEMPexamplecert.CER';
--EXECUTE master.dbo.xp_delete_file  'del C:TEMPexamplecert.CER';
-- create user able to read sys.dm_exec_connections
CREATE USER examplecertuser FROM CERTIFICATE [examplecert2]
GRANT VIEW SERVER STATE TO examplecertuser
GRANT AUTHENTICATE TO examplecertuser --?
go
USE certtest
--GRANT SELECT ON testtbl TO examplecertuser
--go
-- Sign the procedure.
ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert
    WITH PASSWORD = 'All you need is love'
go
-- Run as the test user, to actually see that this works.
EXECUTE AS USER = 'testuser'
go
-- run the signed procedure. 
EXEC example_sp
go
-- Become ourselves again.
REVERT
go
-- Clean up
USE master
DROP DATABASE certtest
DROP LOGIN testuser
DROP USER examplecertuser
DROP CERTIFICATE examplecert2

仍然没有测试用户错误的权利。修复建议或工作示例（如果可能）将不胜感激））。提前感谢！

Answer 1

这

似乎有效...

-- Create a test login and test database
CREATE DATABASE certtest
go
-- Move to the test database.
use certtest
go
CREATE LOGIN testuser WITH PASSWORD = 'CeRT=0=TeST'
CREATE USER testuser FROM LOGIN testuser  -- Create the test user.
go
CREATE PROCEDURE example_sp 
    @ip VARCHAR(48) OUTPUT
    AS
BEGIN   
   SELECT  @ip = client_net_address FROM  sys.dm_exec_connections   WHERE   session_id = @@SPID   
END
go
GRANT EXECUTE ON example_sp TO public
go
-- Create the certificate.
go
CREATE CERTIFICATE examplecert
   ENCRYPTION BY PASSWORD = 'All you need is love'
   WITH SUBJECT = 'Certificate for example_sp',
   START_DATE = '20020101', EXPIRY_DATE = '20200101'
go
-- transfer certificate to master
BACKUP CERTIFICATE [examplecert] TO FILE = 'C:TEMPexampleCert.CER';
GO
USE [master]
GO
CREATE CERTIFICATE [examplecert] FROM FILE = 'C:TEMPexamplecert.CER'
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'examplecertuserz')
BEGIN
DROP USER [examplecertuserz] 
DROP LOGIN [examplecertuserz] 
END
go
CREATE LOGIN [examplecertuserz]
    FROM CERTIFICATE examplecert
GO
CREATE USER [examplecertuserz]
REVOKE CONNECT SQL FROM [examplecertuserz]
GO 
GRANT AUTHENTICATE SERVER TO [examplecertuserz]
GO
GRANT VIEW SERVER STATE TO [examplecertuserz]
go
USE certtest
go
ADD SIGNATURE TO example_sp BY CERTIFICATE examplecert  WITH PASSWORD =  'All you need is love'
go
EXECUTE AS LOGIN = 'testuser'
go
-- Then run the signed procedure. 
declare @ip as varchar(48)
exec example_sp @ip OUTPUT
select @ip
go
-- Become ourselves again.
REVERT
go
-- Clean up
USE master
DROP DATABASE certtest
DROP LOGIN testuser
DROP LOGIN examplecertuserz
DROP USER examplecertuserz
DROP CERTIFICATE examplecert