SQL Server Performance

xp_cmdshell access permissions

Discussion in 'General DBA Questions' started by Raja.V, Sep 13, 2007.

  1. Raja.V New Member

    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
    --
    --
  2. ghemant Moderator

  3. satya Moderator

    Check the password for that proxy account si valid or not?

Share This Page