SQL Security Fundamentals: Changing the Execution

Excerpt by Don Kiely | June 21, 2013


You don't always want the caller's permissions to be used to validate permissions in a broken ownership chain. Sometimes you want to execute the code as though it was being executed by another user entirely, by using that other user's permissions to validate permissions on all of the accessed objects.

This is called switching the execution context of the code. This lets you take advantage of SQL Server's granular permissions, keeping tight control over the permissions to underlying objects, while giving various users the ability to execute the code.

In SQL Server, when you define any kind of user-defined functions (except inline table-valued functions), stored procedures, and data manipulation triggers, you can use the EXECUTE AS clause as part of the definition of the code to run the code under the security context of the specified user.

Four EXECUTE AS options are available:

  • EXECUTE AS CALLER: The default for backward compatibility. The code executes in the context of the caller of the code, who must have permissions both to execute the code and to access underlying objects. The actual behavior depends on whether the ownership chain is broken or unbroken.
  • EXECUTE AS= 'username' and EXECUTE AS = 'loginname': The code executes in the context of the specified user or login, so that the user must have permissions on underlying objects. In this case, the caller must either:
    • Have execute permission on the code.
    • Be a sysadmin or db_owner, or have CONTROL SERVER >permissions on the server or the database, or have impersonate permission for the username.>
  • EXECUTE AS SELF: A shortcut notation for the current user who is creating the procedure. This is equivalent to EXECUTE AS USER = [myUserName]. The SQL Server catalog stores the actual user ID of the person who writes the code.
  • EXECUTE AS OWNER: Another variation of execution under the security context of a specific user, in this case the owner of the code at the time of execution. If the owner changes after the code is created in the database, it is the current owner when the code executes.
TIP: Any time the security context of the code changes through EXECUTE AS, the creator of the code must have the IMPERSONATE permission for the user specified in the clause. You don't ever need to have this permission to impersonate yourself, however, such as for EXECUTE AS SELF.

ldn-expertdkielyThis post is an excerpt from the online courseware for our SQL Server 2012: Security Fundamentals course written by expert Don Kiely.

Don Kiely

Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.

This course excerpt was originally posted June 21, 2013 from the online courseware SQL Server 2012, Part 5 of 9: Security Basics by Don Kiely