“EXECUTE AS” clause in T-SQL

In T-SQL there is really interesting clause available which allows DB engine to execute some particular module (function, SP, trigger etc) with context to some user. This clause is very handy when we don’t want some specific user to have access to some schema but we still want that user to allow access to data from that schema.

MSDN describes this situation as,

By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.

User can use “execute as” clause different arguments, which are “CALLER”, “SELF”, “OWNER”, “user_name” and “login_name”. All details are well explained on MSDN so I am not going to explain use of all arguments. But I am particularly interested in “user_name” argument. Use of this argument allows us to execute specified module in context to some user name. Wait, does it means that if I create a SP for accounting schema with context to admin will I have access to that schema ?? Not necessarily, to be able to execute any module that uses this clause requires me to have EXECUTE permission for this module. Also you will also need to have IMPERSONATE permission for module if you want to use this clause in your module.

With this basic intro, I’ve created sample scripts to test this feature. I’ve create a test DB and one schema and a user who doesn’t have access to this schema, but have execute permission of DB.

Create a schema for test DB, (userFOO is owner of this schema)

CREATE SCHEMA [human] AUTHORIZATION [userFoo]
GO

Then create a table under this schema, and populate this table with some data

CREATE TABLE [human].[employeeeInfo](
    [column1] [int] NULL,
    [column2] [varchar](50) NULL
) ON [PRIMARY]

GO

declare @i int
set @i= 1
while @i < 100
begin
insert into[ human].[employeeeInfo]
values (@i,'abc abc')
set @i = @i + 1
end
GO
select * from human.employeeeInfo
GO

Create a proc to access data in this table,

create proc human.selectEmp
with execute as 'userFoo'
as
begin
select * from human.employeeeInfo
end
go

 

Create a login and user, userBar

USE [master]
GO
CREATE LOGIN [userBar] WITH PASSWORD=N'userBar', DEFAULT_DATABASE=[TestDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [TestDB]
GO
CREATE USER [userBar] FOR LOGIN [userBar]
GO
USE [TestDB]
GO
ALTER USER [userBar] WITH DEFAULT_SCHEMA=[dbo]
GO

Grant user EXECUTE permission of DB,

use [TestDB]
GO
GRANT EXECUTE TO [userBar]
GO

Now, log in as a userBar and run following query,

EXEC human.selectEmp

SELECT * FROM human.employeeInfo

You will see that first query will return 100 rows but second one will generate error stating that user doesn’t have permission to access this schema.table. object !! When this clause is included in module, DB engine evaluates permission by using something called “Ownership Chains” which is basically method employed by SQL Server to allow one user to access objects owned by another user.

I’ve uploaded this sample script here.

It’s Just A Thought …