The Meaning of Stored Procedures Part 7 — No direct owners, name resolutions and the difference between EXECUTE AS SELF and EXECUTE AS OWNER

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.

Name resolution

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:

USE AdventureWorks2008R2
GO

CREATE USER Test WITHOUT LOGIN;
GRANT SELECT ON [dbo].[ErrorLog] TO Test;
GO

SELECT name, default_schema_name 
FROM sys.database_principals  
WHERE name in(USER_NAME(),''Test'');
GO
-----
name    default_schema_name
dbo     dbo
Test    dbo

 

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):

EXECUTE AS USER = ''Test'';
GO
SELECT *
FROM ErrorLog;
GO
SELECT *
FROM Department;
GO
SELECT *
FROM HumanResources.Department;
GO
REVERT;
GO
------
(0 row(s) affected)
Msg 208, Level 16, State 1, Line 1
Invalid object name ''Department''.
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object ''Department'', database ''AdventureWorks2008R2'', schema ''HumanResources''.

 

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:

CREATE PROC uspExecutionContext
AS
SELECT name, type 
FROM sys.user_token;
GO

 

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:

GRANT EXECUTE ON uspExecutionContext TO Test;
GO

DECLARE @ID INT;
SET @ID = DB_ID(); 
DBCC FLUSHPROCINDB  (@ID);
GO

 

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:

EXECUTE uspExecutionContext;
GO
EXECUTE AS USER = ''Test'';
EXECUTE uspExecutionContext;
REVERT;
GO
-----
name    type
dbo     WINDOWS LOGIN

Test     SQL USER
public   ROLE

 

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:

SELECT execution_count ,plan_handle
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE text LIKE ''%uspExecutionContext%''
AND text NOT LIKE ''%dm%'';
GO
-----
execution_count    plan_handle
2                  0x05000C007E441831A0BBBDCC0400000001000000000000000000000000000000000000000000000000000000

 

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:

SELECT *
FROM SYS.dm_exec_plan_attributes (0x05000C007E441831A0BBBDCC0400000001000000000000000000000000000000000000000000000000000000);

 

image

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:

ALTER USER Test
WITH DEFAULT_SCHEMA = HumanResources;

 

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:

DECLARE @ID INT
SET @ID = DB_ID() 
DBCC FLUSHPROCINDB  (@ID);
GO

SELECT *
FROM ErrorLog;
GO

EXECUTE AS USER = ''Test'';
GO
SELECT *
FROM ErrorLog;
GO
REVERT;
GO

 

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:

EXECUTE AS USER = ''Test'';
GO
SELECT *
FROM ErrorLog;
GO
REVERT;
GO

SELECT execution_count ,plan_handle
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE text LIKE ''%ErrorLog%''
AND text NOT LIKE ''%dm%'';
GO

SELECT *
FROM SYS.dm_exec_plan_attributes (0x06000C0017382113101159F70400000001000000000000000000000000000000000000000000000000000000)
WHERE attribute = ''user_id''
UNION ALL
SELECT *
FROM SYS.dm_exec_plan_attributes (0x06000C0017382113B00859F70400000001000000000000000000000000000000000000000000000000000000)
WHERE attribute = ''user_id''
GO
-------
attribute    value    is_cache_key
user_id       5        1
user_id       1        1

 

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:

DBCC FREEPROCCACHE
GO

SELECT *
FROM dbo.ErrorLog;
GO

EXECUTE AS USER = ''Test'';
GO
SELECT *
FROM dbo.ErrorLog;
GO
REVERT;
GO

SELECT execution_count ,plan_handle
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE text LIKE ''%ErrorLog%''
AND text NOT LIKE ''%dm%''
------
execution_count    plan_handle
2                  0x06000C009A38022DB00859F70400000001000000000000000000000000000000000000000000000000000000

 

To finish this example, check the user_id value stored with this reused query plan:

SELECT *
FROM SYS.dm_exec_plan_attributes (0x06000C009A38022DB00859F70400000001000000000000000000000000000000000000000000000000000000)
WHERE attribute = ''user_id'';
GO
-------
attribute    value    is_cache_key
user_id       -2       1

 

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:

CREATE PROCEDURE uspErrorLog AS
SELECT UserName,ErrorMessage, ErrorTime
FROM ErrorLog;
GO

GRANT CONTROL ON uspErrorLog TO Test;
GO

DECLARE @ID INT
SET @ID = DB_ID() 
DBCC FLUSHPROCINDB  (@ID);
GO

EXECUTE uspErrorLog;
GO
EXECUTE AS USER = ''Test'';
EXECUTE uspErrorLog;
REVERT;
GO

SELECT execution_count ,plan_handle
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE text LIKE ''%uspErrorLog%''
AND text NOT LIKE ''%dm%'';
GO

SELECT *
FROM SYS.dm_exec_plan_attributes (0x05000C0062D5E83490E258F70400000001000000000000000000000000000000000000000000000000000000);
-------
execution_count    plan_handle
2                 0x05000C0062D5E83490E258F70400000001000000000000000000000000000000000000000000000000000000

 

image

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:

EXECUTE AS USER = ''Test'';
GO

ALTER PROCEDURE uspErrorLog 
WITH EXECUTE AS self
AS
SELECT UserName,ErrorMessage, ErrorTime
FROM dbo.ErrorLog;
GO

REVERT
GO

 

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:

EXEC uspErrorLog;
GO

 

But if we try to change execution context of this stored procedure by using EXECUTE AS OWNER statement, we will get an error:

EXECUTE AS USER = ''Test'';
GO

ALTER PROCEDURE uspErrorLog 
WITH EXECUTE AS owner
AS
SELECT UserName,ErrorMessage, ErrorTime
FROM dbo.ErrorLog;
GO

REVERT
GO
-------
Msg 15151, Level 16, State 1, Procedure uspErrorLog, Line 2
Cannot execute as the user ''dbo'', because it does not exist or you do not have permission.

 

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

Share this article ...

Google Plus
Ihren LinkedIn Kontakten zeigen



This entry was posted in SQL Server, Stored Procedures and tagged , , , , , by Marcin Szeliga. Bookmark the permalink.
Marcin Szeliga

About Marcin Szeliga

Since 2006 invariably awarded Microsoft Most Valuable Professional title in the SQL category. A consultant, lecturer, authorized Microsoft trainer with 15 years’ experience, and a database systems architect. He prepared Microsoft partners for the upgrade to SQL Server 2008 and 2012 versions within the Train to Trainers program. A speaker at numerous conferences, including Microsoft Technology Summit, SQL Saturday, SQL Day, Microsoft Security Summit, Heroes Happen {Here}, as well as at user groups meetings. The author of many books and articles devoted to SQL Server.

2 thoughts on “The Meaning of Stored Procedures Part 7 — No direct owners, name resolutions and the difference between EXECUTE AS SELF and EXECUTE AS OWNER

  1. Good post however I was wanting to know if you could write a litte more on this subject? I”d be very thankful if you could elaborate a little bit further. Appreciate it!