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!

Signature
Steven Bras
Tessitura Network, Inc.
Charles Wang[MSFT] - 22 Sep 2006 08:08 GMT
Dear Steven,
My understanding of your issue is that:
You could execute xp_cmdshell without specify any rights in your stored
procedure in SQL Server 2000; however xp_cmdshell failed to execute in your
stored procuedure due to permission limiation. You can run it by specifying
EXECUTE AS clause for CREATE PROCEDURE, but you want a way that can have
the stored procedure work on both SQL 2000 and 2005.
If I have misunderstood, please let me know.
SQL Server 2005 enhances the security feature. By default, it is required
to execute the xp_cmdshell command in a permissible context. As you
mentioned, you can execute it in your stored procedure by specifying
EXECUTE AS clause. This method can succeed because of the context switch.
From your description, I think you want to keep the transplant-ability of
your stored procedure between SQL Server 2000 and SQL Server 2005. I
recommend that you use sp_xp_cmdshell_proxy_account to assign a proxy
account with local administrator permission for running xp_cmdshell, so
that you needn't change your stored procedure.
For example:
EXEC sp_xp_cmdshell_proxy_account 'ADVWKS\Max04', 'YourPassword!";
GO
For more information of sp_xp_cmdshell_proxy_account, you can refer to SQL
Server Books Online.
If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.
Charles Wang
Microsoft Online Community Support
======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
Dan Guzman - 22 Sep 2006 13:51 GMT
> 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.

Signature
Hope this helps.
Dan Guzman
SQL Server MVP
> 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
[quoted text clipped - 11 lines]
>
> Thanks!