RE: XP_CMDSHELL and DB Chaining

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.
======================================================

.

Relevant Pages

  • Re: Minimum Permissions for sp_adduser
    ... It turns out that db_owner will not do, the account must actually be the owner of the database and therefore the 'dbo' user. ... "sp_adduser" is hardcoded to reject even these rights. ... But if you change to the SQL Server 2000 preferred command, "sp_grantdbaccess" it will work for you. ... But still i am not able to execute sp_adduser using this new user. ...
    (microsoft.public.sqlserver.security)
  • Re: Minimum Permissions for sp_adduser
    ... But the OP is using SQL Server 2000 ... I suggested to him adding the user into securityadmin server role and try ... But still i am not able to execute sp_adduser using this new user. ... stored procedure will not add the rights to a non-dbo. ...
    (microsoft.public.sqlserver.security)
  • Re: Startup.cmd
    ... I can't recall ever having tested this in an OS/2 session with earlier versions of OS/2, but both eCS1.2 and MCP1 refuse to execute a hidden cmd or exe file. ... If it works in a DOS session, ... You can either set these options at the dir command or by ... However if a bat or exe file is hidden it can still be executed in the usual way by specifying its name on the command line. ...
    (comp.os.os2.bugs)
  • Re: Opening an Excel Spreadsheet
    ... >>What you mean is starting based on file type. ... > This allows you to execute excel.exe without specifying the path and ...
    (microsoft.public.win2000.cmdprompt.admin)
  • error ASP 0113
    ... error 'ASP 0113' ... The maximum amount of time for a script to execute was exceeded. ... can change this limit by specifying a new value for the property ...
    (microsoft.public.windowsxp.perform_maintain)