Wednesday, August 3, 2011

Database TRUSTWORTHY Property

The TRUSTWORTHY database property indicates whether the SQL instance trusts the database to access objects external to the database.  When the TRUSTWORTHY property is set to ON, it allows database modules that use an impersonation context to have access to resources outside the database. 

For example, user-defined functions, stored procedures, or assemblies may access resources outside the database.   Also, impersonation of database level principals or users (known as extending database impersonation) by using the EXECUTE AS USER statement allows access outside the database.

When migrating a database that requires external access (and the TRUSTWORTHY property is ON), you need to reset the property after migration.  Since the SQL instance does not automatically trust databases as a default, all databases are set to TRUSTWOTHY OFF when created or attached. 

To set the TRUSTWORTHY property:
ALTER DATABASE <database name>
SET TRUSTWORTHY { ON | OFF }


Modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.

No comments:

Post a Comment