trustworthy setting for the database

Question

Hi All,

I have a lot of databases with trusthworthy settings turned on.
Is there a way we can monitor whether we are really using it so that we can disable it if not used.

Thanks,
ACDBA

Answer 1

You can use SQL Server Auditing (DATABASE_PRINCIPAL_IMPERSONATION_GROUP, SERVER_PRINCIPAL_IMPERSONATION_GROUP, DATABASE_PRINCIPAL_IMPERSONATION_GROUP) to monitor when is used. Read the documentation here.

Answer 2

In addition to Alberto's post, check if these database have any CLR assemblies. They may also require the TRUSTWORTHY setting. Or precisely, someone thought they need. There other ways to establish trust for CLR assemblies. And better.

I would also run

   SELECT COUNT(*) FROM sys.sql_modules WHERE execute_as_principal_id IS NOT NULL  

This will reveals modules with the EXECUTE AS clause. I am not sure that Auditing captures these. And it's a lot quicker than turning on auditing and see if something appears.