There is surprisingly much misunderstanding about schema-user separation, and about how name resolution as well as impersonation work in SQL Server. The ability to impersonate users by code modules was added in SQL Server 2005, along with database schemas (SQL Server 2000 only allowed context switching by executing SETUSER statement), but those features are still rarely used. Hence, in this post we are going to examine the difference between a stored procedure creator, owner and its default schema. But first, we will see how name resolution works inside and outside stored procedures and what user-schema separation really means.
Note. SETUSER should be avoided for two reasons: It requires sysadmin privileges and this kind of impersonation is not stackable.
Since SQL Server 2005 every user has a default schema associated with them — if you don’t specify it, this default schema will be “dbo”. To see this, let’s create a user in AdventureWorks2008R2 database, grant them a SELECT permission and check its default schema, as well as default schema of this database owner:
For now, lets assume that every time when an object name is used in a query alone (without a schema name), SQL Server will:
· Check if this object exists in the default user’s schema.
· If not, try to find this object in the dbo schema.
· If not, raise an “Invalid object name” error.
Those queries demonstrate this behavior (note that the first executes successfully because there is an ErrorLog table in the dbo schema, the second one fails because there is no Department table in the dbo schema, and the last one fails as Test user has not got the required permissions):
Note. User-schema separation means that an object (i.e. a table) is no longer owned by any user; it belongs to a schema instead. In turn, the schema is owned by a user or by a role. To see what that means and what the consequences are, please carry on reading.
Default schemas, stored procedures and plan reusing
By default, code modules (like stored procedures) are executed in the security context of the caller of the module. This behavior is simple to see, all you need to do is create a stored procedure that will return name (or names, because you will see shortly that there can be more than one of so-called secondary identities) of currently impersonated users:
Let me grant EXECUTE permission to the Test user on this stored procedure. Additionally, I am going to clear the procedure cache for AdventureWorks2008R2 database:
With this, we are ready to test SQL Server default behavior by executing this stored procedure twice, the first time as a database owner, the second one as a Test user:
Until now, we saw nothing particularly interesting or surprising — our test stored procedure was executed successfully under two different execution contexts.
Now it is the time to correlate this basic information with the previous parts of this series, especially with the first (Plan Caching and Reuse is a Good Thing) and the second (Avoiding recompilations due to plan stability-related reasons) ones.
To do this, we will need an execution plan handle for our stored procedure:
What I really want to stress here, is that this execution plan was used twice. So, SQL Server reused it regardless of the user who actually executed our stored procedure. As we know, reusing execution plans is a desired behavior, so what is the point of this example?
To answer this question, we will have to check attributes of this stored procedure execution plan:
Remember, all attributes marked as cache keys are stored with execution plans and any change of those attribute values means that the cached plan will not be reused for this specific execution. The interesting attribute is the user_id one (this is also one of caching plan attributes that was not covered in the previous parts of this series).
At first look the user_id attribute seems to be a little strange. At least I was puzzled. It has value 1, but we already executed this stored procedure as two different users. And there are two reasons for this:
· Firstly, this attribute is misnamed, it is not a user id, it’s the default schema id for that user.
· Secondly, stored procedures are by default executed in the security context of the caller. However the security context is not the same thing as a name resolution one. And as far as name resolution is concerned, SQL Server executes stored procedures in the context of default schema of their owners.
Lesson learned — It is the users’ default schema that is important for name resolution and plan reusing, not the users nor their names. And the whole thing is even more complicated with stored procedures — in those case the default schema of the owner of the stored procedure is important, users’ default schema has no meaning.
Two-part names and plan reusing
Before we go further, let me take a step back and clarify the impact of user_id attribute (as we already know, it represents the id of the default schema assigned to the user) on plan reusing.
To do this, I have to change the Test user default schema:
If we select data from the same table twice, one as a dbo and then as a Test user, we will find in the cache two different execution plans for the exactly the same query:
And by checking those plan attributes, we will find that for the first one the user_id value is 1, but for the second one it’s 5:
Seems like if users have different default schemas, their queries will not reuse execution plans. But this is not completely true. All we need to do in order to avoid unnecessary recompilation is use two-part names. This way there will be no ambiguity and SQL Server will not have to perform name resolution:
To finish this example, check the user_id value stored with this reused query plan:
Let me quote the BOL:
Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. This is the preferred method. Any other value represents the user ID of the user submitting the query in the database.
Lesson learned — if users have different default schemas, their queries will not reuse execution plans unless the two part names are used.
Back to the stored procedures
As we already know, stored procedures change the way that name resolution works — the default schema of the owner of the stored procedure is used in this process instead of the caller default schema. This allows for better plan reusing, i.e. even though the Test user default schema was changed, subsequent executions will reuse the same plan. And this is true even if inside a stored procedure one-part name was used:
EXECUTE AS OWNER vs EXECUTE AS SELF
SQL Server allows us to change the execution context of code modules. Remember, the context in which the module is executed has no direct relationship to the name resolution process. But there is also another misconception — as long as EXECUTE AS [CALLER/USER] are pretty obvious, the difference between EXECUTE AS OWNER and EXECUTE AS SELF is often not clear.
To see this difference, let me impersonate the Test user and alter our stored procedure:
By specifying EXECUTE AS SELF, this stored procedure will now run under the Test user security context (the user who created or altered the code module). And this work fine:
But if we try to change execution context of this stored procedure by using EXECUTE AS OWNER statement, we will get an error:
And this error message tells the story — the owner of this procedure is the owner of the schema to which it belongs, not the user who created or altered it.
Lesson learned — As a result of user-schema separation the majority of database objects do not have a specified owner (unless you explicitly set ownership with ALTER AUTHORIZATION statement) . This also applies to stored procedures. And if the code module does not have a specified owner, it is owned by the owner of the schema of the module.
I hope this clarifies the whole thing