Error message when you use an EXECUTE AS clause in a DDL trigger: "The server principal LoginName is not able to access the database DatabaseName under the current security context" (913422)

SYMPTOMS

In Microsoft SQL Server 2005, when you run a Transact-SQL statement to raise a data definition language (DDL) trigger, you receive an error message that is similar to the following:

Msg 916, Level 14, State 1, Procedure trig001, Line 0
The server principal "LoginName" is not able to access the database "DatabaseName" under the current security context.

Note LoginName represents the name of the login that you impersonate. DatabaseName represents the name of the database.

This issue occurs when all the following conditions are true:

The owner of the database is a Windows authenticated user.
You create the DDL trigger by using a login that has the control server permissions, and no user is mapped to this login in the database.
You use the EXECUTE AS SELF clause or you specify the module to execute as the login in the DDL trigger.

CAUSE

This issue occurs because SQL Server strips down the server-scoped permissions when you impersonate a database user.

For security considerations, the server-scoped permissions are stripped down when you impersonate a database user unless the system administrator has explicitly set SQL Server to trust the impersonated context at the server-scope. In this case, a login with the control server server-scoped permission has no permissions to access any particular database. Therefore, the trigger module that is executed as this login cannot run.

WORKAROUND

To work around this issue, use one of the following methods:

Establish the appropriate trust to the impersonated login in the database. You can establish the trust by using digital signatures or by using the TRUSTWORTHY property of the database.

For more information about how to use digital signatures, see the following topics in SQL Server 2005 Books Online:
- ADD SIGNATURE (Transact-SQL)
- DROP SIGNATURE (Transact-SQL)
For more information about how to use the TRUSTWORTHY property of a database, see the following topics in SQL Server 2005 Books Online:
- TRUSTWORTHY Database Property
- ALTER DATABASE (Transact-SQL)
Grant explicit permissions for the login on the particular database. For more information about how to grant database permissions, see the "GRANT Database Permissions (Transact-SQL)" topic in SQL Server 2005 Books Online.

REFERENCES

For more information about how to impersonate a login by using the EXECUTE AS clause in SQL Server 2005, see the following topics in SQL Server 2005 Books Online: