Avoid using SET TRUSTWORTHY ON

Question

I work with a system that must create objects in one database based on objects created in another database. Objects are not duplicated, so I can’t just replicate objects.

I have the code below that gives a simplified demonstration of what I'm trying to do. If you uncomment the instructions ALTER DATABASE, then it will work without any errors. This has the potential to create a security hole, so I would like to avoid this if possible.

I tried using certificates and impersonation but nothing works. I think the DDL trigger ignores most of the security when it comes to users and logins. I also tried to create a stored procedure in Test_DB_2 that calls the SP in Test_DB_1 and instead has a trigger call to this stored procedure, but that didn't help either.

So, your task, if you agree to accept it, is to make the code work without setting TRUSTWORTHY ON (or by turning on the db chain if this has any effect).

Thanks for any help you can give!

/************************
   SET-UP THE TEST
************************/
USE master
GO
CREATE LOGIN Test_Security_Login WITH PASSWORD = 'p@ssw0rd1!'
CREATE DATABASE Test_DB_1
CREATE DATABASE Test_DB_2
GO
USE Test_DB_1
GO
CREATE PROCEDURE dbo.Create_View
AS
BEGIN
 EXEC('CREATE VIEW Test_View AS SELECT 1 AS one')
END
GO
CREATE USER Test_Security_User FOR LOGIN Test_Security_Login
GRANT EXECUTE ON dbo.Create_View TO Test_Security_User
GO
USE Test_DB_2
GO
CREATE TRIGGER DDL_TRIGGER ON DATABASE WITH EXECUTE AS 'dbo' FOR DDL_VIEW_EVENTS
AS
BEGIN
 EXEC Test_DB_1.dbo.Create_View
END
GO
CREATE USER Test_Security_User FOR LOGIN Test_Security_Login
EXEC sp_addrolemember 'db_ddladmin', 'Test_Security_User'

/************************
   RUN THE TEST
************************/
USE Test_DB_2
GO
--ALTER DATABASE Test_DB_1 SET TRUSTWORTHY ON
--ALTER DATABASE Test_DB_2 SET TRUSTWORTHY ON
EXECUTE AS USER = 'Test_Security_User'
GO
CREATE VIEW dbo.Test_View_2 AS SELECT 2 AS two
GO
REVERT
GO

/************************
   CLEAN-UP
************************/
USE master
GO
DROP DATABASE Test_DB_1
DROP DATABASE Test_DB_2
DROP LOGIN Test_Security_Login
GO

Answer 1

Too easy. Use code signature:

• create self signed certificate in db1
• db2 ( / )
• db2
• AUTHENTICATE .

. . .