AnsweredAssumed Answered

Access Denied While Installing DB Schema

Question asked by ca.portal.admin on Mar 30, 2010
Latest reply on May 7, 2010 by Chris_Hackett
Hi. Was wondetring if anyone has encountered this before?  - Windows Server 2008, MSSQL 2005- After setting up the niku db- Ran the nikuadmin install db command- Error encountered with Access Denied- Suspected error from msdb- Tried by adding SQLAgentUserRole under msdb for niku login- Still encountered the same error  Has anyone encountered this before?      3/30/10 2:35 PM (admin) Process - procedure: CMN_DB_JOB_ADD_SP.xml
3/30/10 2:35 PM (admin) java.sql.SQLException: [CA Clarity][SQLServer JDBC Driver][SQLServer]Access denied.
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.base.BaseExceptions.createException(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.base.BaseExceptions.getException(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processErrorToken(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processReplyToken(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.sqlserver.tds.TDSRequest.processReply(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.sqlserver.SQLServerImplStatement.getNextResultType(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.base.BaseStatement.commonTransitionToState(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.base.BaseStatement.postImplExecute(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.base.BaseStatement.commonExecute(Unknown Source)
3/30/10 2:35 PM (admin) SQL Text:
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.base.BaseStatement.executeInternal(Unknown Source)
3/30/10 2:35 PM (admin)      at com.ca.clarity.jdbc.base.BaseStatement.execute(Unknown Source)
3/30/10 2:35 PM (admin) CREATE PROCEDURE CMN_DB_JOB_ADD_SP (@P_JOB_NAME NVARCHAR(100),
3/30/10 2:35 PM (admin)      at com.niku.dbtools.Utilities.executeAndCommitSql(Utilities.java:1781)
3/30/10 2:35 PM (admin)                                                                           @P_JOB_COMMAND NVARCHAR(4000))
3/30/10 2:35 PM (admin)      at com.niku.dbtools.DriverApp.apply(DriverApp.java:1207)
3/30/10 2:35 PM (admin) AS
3/30/10 2:35 PM (admin)      at com.niku.dbtools.DriverApp.installSchemaDriver(DriverApp.java:590)
3/30/10 2:35 PM (admin) BEGIN
3/30/10 2:35 PM (admin)      at com.niku.dbtools.Utilities.run(Utilities.java:1300)
3/30/10 2:35 PM (admin)     BEGIN TRANSACTION
3/30/10 2:35 PM (admin)      at com.niku.dbtools.Utilities.main(Utilities.java:632)
3/30/10 2:35 PM (admin)     DECLARE @V_JOB_ID BINARY(16),
3/30/10 2:35 PM (admin)                     @V_ERROR_CODE INTEGER,
3/30/10 2:35 PM (admin)                     @V_RETURN_CODE INTEGER,
3/30/10 2:35 PM (admin)                     @V_DB_NAME NVARCHAR(100),
3/30/10 2:35 PM (admin)                     @V_ERROR_TEXT NVARCHAR(1000),
3/30/10 2:35 PM (admin)                     @V_ERROR_LOCATION NVARCHAR(1000)
3/30/10 2:35 PM (admin)     SELECT   @V_RETURN_CODE = 0        
3/30/10 2:35 PM (admin)     SELECT   @V_JOB_ID = 0        
3/30/10 2:35 PM (admin)     SELECT   @V_ERROR_LOCATION = 'Checking for existing job'        
3/30/10 2:35 PM (admin)     SELECT   @V_DB_NAME = db_name()
3/30/10 2:35 PM (admin)      select      @V_JOB_ID = job_id
3/30/10 2:35 PM (admin)      from            msdb.dbo.sysjobs_view
3/30/10 2:35 PM (admin)      where      name = @P_JOB_NAME
3/30/10 2:35 PM (admin)        SELECT @V_ERROR_CODE = @@ERROR
3/30/10 2:35 PM (admin)     IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin)     IF @V_JOB_ID != 0
3/30/10 2:35 PM (admin)     BEGIN
3/30/10 2:35 PM (admin)                SELECT   @V_ERROR_LOCATION = 'Deleting existing job'        
3/30/10 2:35 PM (admin)             EXECUTE msdb.dbo.sp_delete_job @job_name = @P_JOB_NAME
3/30/10 2:35 PM (admin)                    SELECT @V_ERROR_CODE = @@ERROR
3/30/10 2:35 PM (admin)                IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin)     END
3/30/10 2:35 PM (admin)      --
3/30/10 2:35 PM (admin)      --      Have to set the job_id to be null or else sp_add_job thinks
3/30/10 2:35 PM (admin)      --      we're trying to start an MSX job, which we're not.
3/30/10 2:35 PM (admin)      --
3/30/10 2:35 PM (admin)     SELECT   @V_JOB_ID = null        
3/30/10 2:35 PM (admin)     SELECT   @V_ERROR_LOCATION = 'Adding job'        
3/30/10 2:35 PM (admin)     EXECUTE @V_RETURN_CODE = msdb.dbo.sp_add_job
3/30/10 2:35 PM (admin)                                                           @job_id = @V_JOB_ID OUTPUT ,
3/30/10 2:35 PM (admin)                                                           @job_name = @P_JOB_NAME,
3/30/10 2:35 PM (admin)                                                           @delete_level= 3
3/30/10 2:35 PM (admin)        SELECT @V_ERROR_CODE = @@ERROR
3/30/10 2:35 PM (admin)     IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin)     -- Add the job steps
3/30/10 2:35 PM (admin)     SELECT   @V_ERROR_LOCATION = 'Adding job step'        
3/30/10 2:35 PM (admin)     EXECUTE @V_RETURN_CODE = msdb.dbo.sp_add_jobstep
3/30/10 2:35 PM (admin)                                                           @job_id = @V_JOB_ID,
3/30/10 2:35 PM (admin)                                                           @step_id = 1,
3/30/10 2:35 PM (admin)                                                           @step_name = @P_JOB_NAME,
3/30/10 2:35 PM (admin)                                                           @command = @P_JOB_COMMAND,
3/30/10 2:35 PM (admin)                                                           @database_name = @V_DB_NAME,
3/30/10 2:35 PM (admin)                                                           @subsystem = N'TSQL'
3/30/10 2:35 PM (admin)        SELECT @V_ERROR_CODE = @@ERROR
3/30/10 2:35 PM (admin)     IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin) /*   IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin)     EXECUTE @V_RETURN_CODE = msdb.dbo.sp_update_job
3/30/10 2:35 PM (admin)                                                           @job_id = @V_JOB_ID,
3/30/10 2:35 PM (admin)                                                           @start_step_id = 1
3/30/10 2:35 PM (admin)     IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin) */
3/30/10 2:35 PM (admin) /*             */
3/30/10 2:35 PM (admin)     -- Add the job schedules
3/30/10 2:35 PM (admin)     SELECT   @V_ERROR_LOCATION = 'Adding job schedule'        
3/30/10 2:35 PM (admin)     EXECUTE @V_RETURN_CODE = msdb.dbo.sp_add_jobschedule
3/30/10 2:35 PM (admin)                                                           @job_id = @V_JOB_ID,
3/30/10 2:35 PM (admin)                                                           @name = @P_JOB_NAME,
3/30/10 2:35 PM (admin)                                                           @freq_type = 1
3/30/10 2:35 PM (admin)        SELECT @V_ERROR_CODE = @@ERROR
3/30/10 2:35 PM (admin)     IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin)     -- Add the Target Servers
3/30/10 2:35 PM (admin)     SELECT   @V_ERROR_LOCATION = 'Adding job server'        
3/30/10 2:35 PM (admin)     EXECUTE @V_RETURN_CODE = msdb.dbo.sp_add_jobserver
3/30/10 2:35 PM (admin)                                                           @job_id = @V_JOB_ID
3/30/10 2:35 PM (admin)        SELECT @V_ERROR_CODE = @@ERROR
3/30/10 2:35 PM (admin)     IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin)     SELECT   @V_ERROR_LOCATION = 'Start job'        
3/30/10 2:35 PM (admin)     EXEC msdb.dbo.sp_start_job @job_id = @V_JOB_ID
3/30/10 2:35 PM (admin)        SELECT @V_ERROR_CODE = @@ERROR
3/30/10 2:35 PM (admin)     IF (@V_ERROR_CODE 0 OR @V_RETURN_CODE 0) GOTO E_ROLLBACK
3/30/10 2:35 PM (admin)     COMMIT TRANSACTION
3/30/10 2:35 PM (admin)     RETURN (@V_JOB_ID)
3/30/10 2:35 PM (admin) E_ROLLBACK:
3/30/10 2:35 PM (admin)     SELECT @V_ERROR_TEXT = 'CMN_DB_JOB_ADD_SP ' + @V_ERROR_LOCATION + ' - error ' + CONVERT( nvarchar( 1000 ), @V_ERROR_CODE ) + ' return code ' + CONVERT( nvarchar( 1000 ), @V_RETURN_CODE )
3/30/10 2:35 PM (admin)     IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
3/30/10 2:35 PM (admin)      EXEC CMN_RAISERROR_SP 100, -20999, @V_ERROR_TEXT
3/30/10 2:35 PM (admin)      RETURN (0)
3/30/10 2:35 PM (admin) END

Attachments

Outcomes