{"id":221,"date":"2024-01-23T08:55:00","date_gmt":"2024-01-23T13:55:00","guid":{"rendered":"https:\/\/www.sqltabletalk.com\/?p=221"},"modified":"2024-01-21T00:10:25","modified_gmt":"2024-01-21T05:10:25","slug":"understanding-the-implications-of-the-trustworthy-database-setting-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.sqltabletalk.com\/?p=221","title":{"rendered":"Understanding the Implications of the TRUSTWORTHY Database Setting in SQL Server"},"content":{"rendered":"

Introduction<\/strong><\/h2>\n

As a SQL Server DBA, understanding the nuances of database settings is necessary for maintaining security and integrity. One such setting that often becomes a topic of discussion due to its significant impact on security is the TRUSTWORTHY<\/code> database setting. The TRUSTWORTHY<\/code> database setting in SQL Server is a configuration option that affects the security of certain database operations, particularly those involving code execution contexts. It plays a critical role in the security context of modules that use WITH EXECUTE AS<\/code>, as well as CLR (Common Language Runtime) assemblies marked as EXTERNAL_ACCESS<\/code> or UNSAFE<\/code>.<\/p>\n

What Does TRUSTWORTHY Do?<\/h3>\n

When the TRUSTWORTHY<\/code> setting is enabled (set to ON<\/code>) for a database, it allows SQL Server to trust the contents within that database, especially in terms of authentication and authorization for certain types of code execution:<\/p>\n

    \n
  1. Modules Using WITH EXECUTE AS<\/code><\/strong>: This setting impacts the behavior of T-SQL modules (like stored procedures and functions) that use the WITH EXECUTE AS<\/code> clause. When TRUSTWORTHY<\/code> is ON<\/code>, these modules can execute under the context of a specified user, allowing the code to adopt permissions associated with that user. This is particularly relevant for cross-database access.<\/li>\n
  2. CLR Assemblies<\/strong>: For CLR (Common Language Runtime) integrations, TRUSTWORTHY<\/code> enables assemblies marked as EXTERNAL_ACCESS<\/code> or UNSAFE<\/code> to be executed. These CLR assemblies, when running under a database with TRUSTWORTHY ON<\/code>, can access external system resources or perform actions that are otherwise restricted under normal security contexts.<\/li>\n<\/ol>\n

    The TRUSTWORTHY Setting in Action<\/h3>\n

    To grasp the implications of enabling TRUSTWORTHY<\/code>, let’s consider a practical scenario with a database named TrustyDB<\/code>. In this database, we have two logins: Sam and NoPerms. Sam is associated with a user named TrustyUser<\/code>, who has database_owner<\/code> permissions in TrustyDB<\/code>.<\/p>\n

    Attempting to use TrustyUser<\/code> to create and execute a stored procedure to add Sam to the sysadmin role initially fails due to insufficient permissions. The error messages clearly state the inability to execute as the database principal and to alter the server role ‘sysadmin’.<\/p>\n

    However, altering TrustyDB<\/code> to set TRUSTWORTHY ON<\/code> changes the game:<\/p>\n

    \nALTER DATABASE TrustyDB SET TRUSTWORTHY ON\nGO\nEXECUTE AS USER = 'TrustyUser';\nGO\nDECLARE @SQL NVARCHAR(MAX)\nSET @SQL = N'\nEXECUTE(''\nCREATE PROCEDURE GhostProc\nWITH EXECUTE AS OWNER\nAS\nBEGIN\nALTER SERVER ROLE sysadmin ADD MEMBER Sam\nEND'');\nEXECUTE GhostProc; DROP PROCEDURE GhostProc;'\nEXECUTE(@SQL)\n<\/code><\/pre>\n
    This time, the operation is successful, and Sam is added to the sysadmin list. This example demonstrates the power and risks associated with the TRUSTWORTHY<\/code> setting.<\/p>\n
    Caution and Best Practices<\/h3>\n
    While TRUSTWORTHY<\/code> can be a useful tool in SQL Server, its activation must be handled with utmost caution due to the inherent risks it poses:<\/p>\n