Hi everyone .... I am having a problem with a login who is not able to execute the stored procedure which uses xp_cmdshell. For the user called 'tdlinxsql' I have given execute permission to the xp_cmdshell and when I check for the permission by giving the sp_helprotect null,'tdlinxsql' it shows that the user has the permission for the execute access. But when I login as the user 'tdlinxsql' and execute xp_cmdshell it gives me the error msg Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. so here I have created a credential ##xp_cmdshell_proxy_account## in my account where Iam SA for the server with the login of 'tdlinxsql'.Then after I have created proxy account in the same account by giving the proxy account name ##xp_cmdshell_proxy_account## which is same as the credential name and in the principals column where when we carete the proxy I have added 'tdlinxsql' account. I have also checked the credential by giving the select * from sys.credentials and they are present in my account. Now when I login as a 'tdlinxsql' and execute one stored procedure which is suppose to execute some SSIS packages which loads some data in to the tables.Here it gives me an error: ========== TDLinx Load Code Tables Started ========== = tdlinx_account_nostrcd Load Started Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1 The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information. even at this point after creating the credentials iam not able to ecexute the xp_cmdshell if i login as 'tdlinxsql'.it still gives me the same error which I mentioned first. So in this case what else i need to do in order for the stored procedure to execute. Iam attaching the stored procedure with this mail. Thanks in advance, ********************************************************************************************************** USE [prism] GO /****** Object: StoredProcedure [dbo].[rb_sp_TDLinxLoadCodeTables] Script Date: 09/13/2007 15:03:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --IF (EXISTS (SELECT * FROM dbo.sysobjects WHERE name = N'rb_sp_TDLinxLoadCodeTables' AND type='P')) -- DROP PROCEDURE [dbo].[rb_sp_TDLinxLoadCodeTables] --GO --CREATE PROCEDURE rb_sp_TDLinxLoadCodeTables AS CREATE PROCEDURE [dbo].[rb_sp_TDLinxLoadCodeTables] AS --==================================================================================== -- M O D I F I C A T I O N L O G -------------------------------------------------------------------------------------- -- USER ID DATE MODIFICATION DESCRIPTION -------------------------------------------------------------------------------------- -- EEADS 05/07/2007 Procedure Created. --==================================================================================== -- SET XACT_ABORT ON SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET LOCK_TIMEOUT 120000 SET DEADLOCK_PRIORITY LOW -- ------------------------------------------------------------------------- -- Work Variables ------------------------------------------------------------------------- DECLARE @ServerInfo varchar(100) DECLARE @PkgVars varchar(200) DECLARE @TableNameSuffix varchar(50) DECLARE @UserNbr int DECLARE @RetCode int -- SELECT TOP 1 @UserNbr = user_nbr FROM dbo.prism_user WHERE login_name = 'tdlinxsql' SET @PkgVars = '/Set Package.Variables[User::varUserNbr].Properties[Value];"' + cast(@UserNbr as varchar) + '"' -- -- SET @ServerInfo = ' /Ser localhost /U tdlinxsql /P pwd123$$ ' -- -- PRINT ' ' PRINT '========== TDLinx Load Code Tables Started ==========' -- ------------------------------------------------------------------------- -- Execute ACCOUNT NOSTRCD Load ------------------------------------------------------------------------- IF (@@ERROR = 0) BEGIN SET @TableNameSuffix = 'account_nostrcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute ACCOUNT STATUSIND Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'account_statusind' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute ACCOUNT TYPECD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'account_typecd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute ACCOUNT TRANSCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'account_transcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute ACCOUNT TRADECLCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'account_tradeclcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute RETAILSYNC SSTATUSCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'retailsync_sstatuscd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute RETAILSYNC ASTATUSCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'retailsync_astatuscd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute RETAILSYNC TRANSCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'retailsync_transcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute RETAILSYNC EXCEPTION Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'retailsync_exception' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE ANNVOLCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_annvolcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE FOODTYPECD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_foodtypecd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE CHAININD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_chainind' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE LATLONGCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_latlongcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE NOSTRCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_nostrcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE STATUSIND Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_statusind' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE TRANSCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_transcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE FORMATCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_formatcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute STORE TRADECLCD Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'store_tradeclcd' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Execute COMBINED LOOKUP Load ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN SET @TableNameSuffix = 'combined_lookup' EXEC rb_sp_TDLinxLoadCodeTable_Sub @ServerInfo, @PkgVars, @TableNameSuffix, @RetCode OUTPUT END -- ------------------------------------------------------------------------- -- Print completion status message ------------------------------------------------------------------------- IF ((@@ERROR = 0) AND (@RetCode = 0)) BEGIN PRINT '=' PRINT '========== TDLinx Code Load Tables Completed Successfully ==========' END ELSE BEGIN PRINT '*' PRINT '*** TDLinx Load Code Tables Failed ***' END -- --
Hi, Suggest to refer http://msdn2.microsoft.com/en-US/library/aa260700(SQL.80).aspx , http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm Please do not post duplicate thread, I have locked http://sql-server-performance.com/Community/forums/t/23094.aspx