SQL Server 2005’s EXECUTE AS statement

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 =’[email protected]!sh!’
go
create login [SQL2005Shiraishi] from windows
go
use CompanyProducts
go
Create user SQL_Shiraishi for LOGIN Shiraishi
go
Create user WIN_Shiraishi for LOGIN [SQL2005Shiraishi]
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, SQL2005Shiraishi 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

Latest Articles