Scope of sp_executesql

Question

Scope of sp_executesql

ken.trock

SSCertifiable

Points: 5147
More actions
December 16, 2010 at 2:11 pm

#232108

Hi. The following code demonstrates the creation of a user that execute an SP from a DB that reads data from a table in another DB without the user actually having access to the underlying DB. I know this is a bit tedious but it works with cross DB ownership.
/*
exec sp_configure 'cross db ownership chaining', 1
reconfigure with override
exec sp_configure 'cross db ownership chaining'
go
*/
/*
use master
drop login Test1_login
drop database TablesDB
drop database ProcsDB
*/
create login Test1_login with password='Password1'
go ------------------------------------------
create database TablesDB
create database ProcsDB
go
ALTER DATABASE TablesDB SET TRUSTWORTHY ON
ALTER DATABASE ProcsDB SET TRUSTWORTHY ON
go ------------------------------------------
use TablesDB
go
create table t1(c1 int)
insert into t1 values(1)
insert into t1 values(2)
insert into t1 values(3)
go
create user ProcsDB_user for login Test1_login
go ------------------------------------------
use ProcsDB
go
create user ProcsDB_user for login Test1_login
go
create proc p1 as select * from TablesDB..t1
go
GRANT Execute ON dbo.p1 TO [ProcsDB_user];
go ------------------------------------------
select SUSER_SNAME()
Exec ProcsDB..p1
select 'table', * from TablesDB..t1
go ------------------------------------------
execute as user='ProcsDB_user'
go
select SUSER_SNAME()
--works, readonly user has permission the SP
Exec ProcsDB.dbo.p1
go
--fails as it should, readonly user can't see underlying table
select 'table', * from TablesDB.dbo.t1
revert
But change the simple SP to create a varchar to be executed and this test stops working. There must be something about the scope of sp_executesql that causes TablesDB.dbo.t1 to be invisible to our user. Anyone know why this might be?
alter proc p1 as
declare @sql nvarchar(max)
set @sql = 'select * from TablesDB..t1'
exec sp_executesql @sql
go
execute as user='ProcsDB_user'
go
select SUSER_SNAME()
--when run under dynamic sql our Readonly user can't get to the underlying table thru the SP
Exec ProcsDB.dbo.p1
go

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Ownership chaining. Dynamic SQL of any form break that. Hence the 'permissions on base table' requirement if using any form of dynamic SQL.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ken.trock SSCertifiable Points: 5147 More actions · Answer 2

GilaMonster (12/16/2010)
Ownership chaining. Dynamic SQL of any form break that. Hence the 'permissions on base table' requirement if using any form of dynamic SQL.

It's that simple. Good to know, thanks.

Ken