Re: XP_CMDSHELL and DB Chaining

I know about the EXECUTE AS clause for Create Procedure and this does
appear
to work, but we are looking for a method that will work both in 2000 and
2005.

I don't believe there is a single method that will work in both versions.
This is because system objects in SQL 2005 are in the sys schema instead of
the dbo schema and the sys schema is owned by securityadmin. Since you
can't create user objects in the sys schema or create user schema/objects
owned by securityadmin, the ownership chain will always broken between
user/system objects. Consequently, you need to use EXECUTE AS rather than
relying on DB_CHAINING in SQL 2005.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"StevenBr" <sbras@xxxxxxxxxxxxxxxx> wrote in message
news:E4A9DC9B-BD61-402A-AC74-FDDB5DF66F9E@xxxxxxxxxxxxxxxx
In SQL Server 2000, we were successfully able to use database chaining to
allow xp_cmdshell to be called by a stored procedure in our database
without
granting any specific rights directly to xp_cmdshell. In SQL Server 2005,
we
find that this doesn't work and our procedure fails due to incorrect
permissions on xp_cmdshell.

Has something changed in this regard and if so what and how can we fix it?
I know about the EXECUTE AS clause for Create Procedure and this does
appear
to work, but we are looking for a method that will work both in 2000 and
2005.

Thanks!
--
Steven Bras
Tessitura Network, Inc.


.