There are a few things in SQL Server that require you to, or that at least are a lot simpler to make work if you turn the TRUSTWORTHY property of the database you are working with to on. But when you investigate a little further, you will find many articles and posts that warn you that evil will come upon you if you were to indeed turn this setting on. Yet, very few of these sources even attempt to explain what exactly the problem with the TRUSTWORTHY database property is.
With this post and a few more to follow, I am going to attempt to shed some light into the mystery of the TRUSTWORTHY property.
You can see the current TRUSTWORTHY setting for a database by running this query:
To change the property you can use the ALTER DATABASE statement:
You need to be a member of the sysadmin server role to be able to execute this ALTER DATABASE statement.
In Books Online we can read the following about the TRUSTWORTHY database property:
"The TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the database and the contents within it."
This is not very helpful for understanding what is going on. However, there are two areas of concern mentioned:
In this article I am going to look at how the TRUSTWORTHY database property changes the permission set of a stored procedure that is defined with a WITH EXECUTE AS clause.
First we need to create a playground. The following script creates the database TrustDb and adds a few procedures and functions to it that we are going to use later. Careful: Any existing database with this name is dropped first!
The EXEC hlp.DatabaseProperties; at the end lets us know that the database is owned by the login TrustDbOwner and that the TRUSTWORTHY database property is set to off:
Now let's create two logins and users for them in this database:
This also grants the permission to create schemata and procedures in this database to the ChainDbPrincipal2 user.
Next we are going to use those freshly granted permissions to have ChainPrincipal2 create a schema and a procedure:
This creates the procedure Chain.Test and grants execute permission on it to ChainDbPrincipal1, the other user we created previously. The procedure Chain.Test is created WITH EXECUTE AS SELF. That means that every time the procedure is executed, the permissions of the person that created the procedure are in effect. The hlp.ObjectProperties shows that this is indeed the case:
When you create a procedure it by default does not have an owner itself. That is indicated by the NULL value in the object_owner column. That means that ownership is transferred to the owner of the schema this object belongs to. The execute_as_user column tells us that the security context inside the procedure – during its execution – is determined by the permissions of the ChainDbPrincipal2 user.
The procedure itself selects form the hlp.CurrentTokens() functions. This function gets information form the sys.user_token and sys.login_token DMVs. It also uses the USER_NAME() and SUSER_NAME() functions to display the user and login names of the current user.
sys.user_token contains one row for every database principal (user or role) that needs to be considered when checking permissions. For example, if a user user1 is member of the database role role1 this DMV would return one row for the user itself and one row for the role. sys.login_token does the same but for logins and server roles.
Before we continue, let's make sure that the TRUSTWORTHY database property is set to off:
Now we can run the first test:
The EXECUTE AS LOGIN='ChainPrincipal1'; causes SQL Server to behave as if ChainPrincipal1 was used to logon to this connection. The first SELECT shows the current tokens of this login. Than the procedure Chain.Test is executed and the current tokens are selected again:
You can clearly see that the procedure changed the security context completely. Before the procedure call all the tokens where related to the ChainPrincipal1 login as it was used to execute the batch. Within the procedure however all the tokens came from ChainPrincipal2. ChainPrincipal1 is not mentioned anymore. So, even if ChainPrincipal1 had a specific permission granted or denied, inside the procedure that would not matter as only the user and login hierarchies of ChainPrincipal2 are checked.
There is one important difference between the two results. While the first has GRANT OR DENY in the usage column for all four rows, the second result shows DENY ONLY for the two rows that are related to logins. The implication of this is, that any server level permissions granted to the ChainPrincipal2 login are of no use within this procedure. However, if there is something that was explicitly denied, that would take effect during the execution of this procedure.
Now let's turn the TRUSTWORTHY database property is to on:
After that switch let's rerun the same query that we executed before:
This time the result looks like this:
There is no difference in the token list outside of the procedure execution. However, the list from inside the procedure changed dramatically. There are two new user rows and one login row that show the usage AUTHENTICATOR. What the implications of that are will be the topic of another post.
The more important change is that the two login rows that previously showed DENY ONLY now show GRANT OR DENY as well. That means, that any server level permissions that are granted to the ChainPrincipal2 login or the roles it is a member of are in full effect while the procedure is executing.
We investigated how the TRUSTWORTHY database property influences the security context of sql modules that are created with a WITH EXECUTE AS clause. During the execution of a module like this, the security context is completely switched to the one of the principal that was specified in the WITH EXECUTE AS clause. However, server level permissions of that principal apply only if the TRUSTWORTHY database property is set to on.
This behavior allows us to write procedures that execute functionality requiring server level permissions. However it also opens up a few vulnerabilities. Particularly it might allow a malicious person to gain membership in the sysadmin server role. The details of this exploit are topic of the next article. Stay tuned.