SQL Server 2005's EXECUTE AS statement

April 5, 2006

In SQL Server 2005, you can explicitly or implicitly define the execution context. As we all know, a session starts when a user logs on to SQLServer or a connection to SQLserver is made. All operations in that session use the logon credentials used for connecting to SQL Server. When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name.

This is very useful and comes handy for SQL Server 2005 database administrators, when they are checking permissions of a particular user. This is also very helpful when a user wants to execute a stored procedure in the context of another user. This article demonstrates how the "Execute as" statement is useful for Database Administrator.

Let us assume the database administrator, Mr. Smith, has been asked to create a SQL Server login "Shiraishi" and grant access to her windows login "SQL2005/Shiraishi" as well. In addition, he is to give read only permission to a table named products in the Schema CompanyProducts. However, the management does not want Ms. Shiraishi to have access to the table named productprice in the same schema.

Let us assume that we have the following database, CompanyProducts, with CompanyProducts, as shown below.

USE [master]
GO
/****** Object:  Database [CompanyProducts]
  Script Date: 03/26/2006 19:32:40 ******/
IF  EXISTS (SELECT name FROM sys.databases 
  WHERE name = N'CompanyProducts')
DROP DATABASE [CompanyProducts]
go
create database CompanyProducts
go
USE [CompanyProducts]
GO
/****** Object:  Schema [CompanyCustomers]
  Script Date: 03/26/2006 19:33:45 ******/
IF  EXISTS (SELECT * FROM sys.schemas 
  WHERE name = N'CompanyCustomers')
DROP SCHEMA [CompanyCustomers]
go
create Schema CompanyProducts
go
USE [CompanyProducts]
GO
/****** Object:  Table 
  [CompanyProducts].[Products]
  Script Date: 03/26/2006 19:34:32 ******/
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = 
  OBJECT_ID(N'[CompanyProducts].[Products]') 
  AND type in (N'U'))
DROP TABLE [CompanyProducts].[Products]
go
Create table CompanyProducts.Products
(id int, Name varchar(100))
go
insert into CompanyProducts.Products
select 1,'Refrigerator'
go
insert into CompanyProducts.Products
select 2,'Washing Machine'
go
insert into CompanyProducts.Products
select 3,'Dryer'
go
insert into CompanyProducts.Products
select 4,'Lawn Mower'
go
USE [CompanyProducts]
GO
/****** Object:  Table [CompanyProducts].[ProductPrice]
  Script Date: 03/26/2006 19:34:12 ******/
IF  EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[CompanyProducts].[ProductPrice]') 
  AND type in (N'U'))
DROP TABLE [CompanyProducts].[ProductPrice]
go
Create table CompanyProducts.ProductPrice
(id int, Price money)
go
insert into CompanyProducts.ProductPrice
select 1,7000
go
insert into CompanyProducts.ProductPrice
select 2,1000
go
insert into CompanyProducts.ProductPrice
select 3,1000
go
insert into CompanyProducts.ProductPrice
select 4,2500
go

Since Mr. Smith is the database administrator, he logged on to Management Studio using SA as login. Mr. Smith executes the following commands to create a login and user for Ms. Shiraishi.

use master
go
create login Shiraishi with password ='Sh!r@!sh!'
go
create login [SQL2005\Shiraishi] from  windows
go
use CompanyProducts
go
Create user SQL_Shiraishi for LOGIN Shiraishi
go
Create user WIN_Shiraishi for LOGIN [SQL2005\Shiraishi]
go
GRANT SELECT on CompanyProducts.Products to 
SQL_Shiraishi,WIN_Shiraishi
go
DENY SELECT on CompanyProducts.ProductPrice to
SQL_Shiraishi,WIN_Shiraishi
Go

Mr. Smith likes to test the permissions of both the SQL and Windows logins of Ms. Shiraishi. Since the SQL Login has been created by Smith, he knows the password of the login Shiraishi, and is able to test the permission on Login by using the SQLCMD utility or Management Studio.

Unfortunately, SQL2005\Shiraishi is a Windows login and Mr. Smith (or any other sysadmin) does not have her password. Only Ms. Shiraishi has it, and it is not a good practice to ask for a person's password, nor is it a good practice to ask the person to log on to your computer with their credentials to test.

Mr. Smith was able to test the permissions on both the SQL and Windows login by using the new SQL Server 2005 Transact SQL statement "EXECUTE AS" command.

use CompanyProducts
go
Execute as user = 'SQL_Shiraishi'
select * from CompanyProducts.Products
--RESULT
1 Refrigerator
2 Washing Machine
3 Dryer
4 Lawn Mower
select * from CompanyProducts.ProductPrice
--RESULT
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'ProductPrice', database 'CompanyProducts', schema 'CompanyProducts'.

Mr. Smith opened a new query window with his credential and executed the following commands.

Execute as user = 'WIN_Shiraishi'
select * from CompanyProducts.Products
--RESULT
1 Refrigerator
2 Washing Machine
3 Dryer
4 Lawn Mower

select * from CompanyProducts.ProductPrice
--RESULT
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'ProductPrice', database 'CompanyProducts', schema 'CompanyProducts'.

Conclusion

This article demonstrated SQL Server 2005’s new transact SQL statement, "Execute as" and also demonstrated that Execute AS comes in handy for Database Administrators when testing the permissions of a particular user.

» See All Articles by Columnist MAK