SQL Server 'guest' does not have permission to run DBCC CHECKIDENT for object '#TempExtractTableSellIn

mqkwyuun  于 2023-11-16  发布在  其他
关注(0)|答案(1)|浏览(89)
IF  OBJECT_ID(N'TempDb..#TempExtractTableSellIn',N'U') IS NOT NULL  
DROP Table  Tempdb.#TempExtractTableSellIn  
            
 -- Create Temp Table to Hold the delta data while asssgining ISDeleted and PlanCastTransactiionID   
  CREATE TABLE #TempExtractTableSellIn  
       (  
        [PlanCastTransactionID]  [int]  IDENTITY(1,1) PRIMARY KEY NOT NULL ,  
        [FiscalMonthId]    [smallint] NOT NULL,  
        [OriginalBillingMonthId] [smallint] NOT NULL,  
        [RestatementVersionID]  [tinyint] NOT NULL,  
        [SubsidiaryId]    [tinyint] NOT NULL,  
        [SalesTeamId]               [int]       NOT NULL,  
        [ChannelID]     [tinyint] NOT NULL,  
        [ProductFamilyID]   [int]  NOT NULL,  
        [DataSourceId]    [smallint] NOT NULL,  
        [CustomerGroupingId]  [tinyint] NULL,  
        [RevenueStreamID]   [int]  NOT NULL,  
        [LicenseTypeId]    [int]  NOT NULL,  
        [BundleStatusID]   [smallint] NOT NULL,  
        [CurrencyId]    [tinyint] NOT NULL,  
        [RestatementVersioncode] [varchar](4)NOT NULL,  
        [SubsidiaryCode]   [char](3) NOT NULL,  
        [ChannelCode]    [char](3) NOT NULL,  
        [ProductFamilyCode]   [char](5) NOT NULL,  
        [ReportingPricingLevelId]   [smallint] NOT NULL,  
        [DataSourceCode]   [char](5) NOT NULL,  
        [ReportedOrganizationID] [nvarchar](36)NOT NULL,  
        [CustomerGroupingCode]  [char](2) NULL,  
        [RevenueStreamCode]   [char](5) NOT NULL,  
        [SubSegmentID]    [int]  NOT NULL,  
        [LicenseTypeCode]   [char](5) NOT NULL,  
        [CurrencyCode]    [char](3) NOT NULL,  
        [Licenses]     [numeric](16, 6) NULL,  
        [BilledAmount]    [Money]  NULL,  
        [BilledAmountLC]   [Money]  NULL,  
        [BilledAmountCD]   [Money]  NULL,  
        [AdjustedAmount]   [Money]  NULL,  
        [AdjustedAmountLC]   [Money]  NULL,  
        [AdjustedAmountCD]   [Money]  NULL,  
        [BudgetCOGS]    [Money]  NULL,  
        [BudgetCOGSLC]    [Money]  NULL,  
        [BudgetCOGSCD]    [Money]  NULL,  
        [ISDeleted]     [BIT]  DEFAULT 0  
       )  
             
        -----------------------Store PROCESSHISTORY Start-----------------------------  
      SELECT @Action = 'Create',@Mode = '',@Object = '#TempExtractTableSellIn',@ObjectType = 'T'  
        ,@Rows = NULL,@Status = 'Run'  
        ,@LogMsg = 'Create Temporary table to hold the Delta Data for: '+@ExtractSellIn  , @TaskName = NULL   
                                                                    
      EXECUTE Staging.StoreProcessHistory   
        @Process = @SP  
        ,@Status  = @Status  
        ,@Mode    = @Mode    
        ,@Object  = @Object  
        ,@ObjectType = @ObjectType  
        ,@Action = @Action  
        ,@Comment = @LogMsg  
        ,@Debug   = @Debug  
        ,@TaskName = @TaskName  
        ,@WorkFlowId = @WorkFlowId  
        ,@ParentTaskId = @TaskId   
        ,@RunId = @RunId  
        -----------------------Store PROCESSHISTORY END-----------------------------   
  
         
       /*INSERT Values from the Rollup table for new records   */  
       SELECT @MaxPlanCastTranID= COALESCE(MAX(PlancastTransactionID)+1,1) FROM [Outbound].MSSalesBudgetRevenueSellIn WITH (NOLOCK)    
             
       DBCC CHECKIDENT(#TempExtractTableSellIn,RESEED,@MaxPlanCastTranID)  
                                      
       INSERT INTO #TempExtractTableSellIn WITH (TABLOCK)   
       SELECT    
        RS.[FiscalMonthId]  
        ,RS.[OriginalBillingMonthId]  
        ,RS.[RestatementVersionID]  
        ,RS.[SubsidiaryId]  
        ,RS.[SalesTeamId]  
        ,RS.[ChannelID]  
        ,RS.[ProductFamilyID]  
        ,RS.[DataSourceId]  
        ,ISNULL(RS.[CustomerGroupingId],0)   
        ,RS.[RevenueStreamID]  
        ,RS.[LicenseTypeId]  
        ,RS.[BundleStatusID]  
        ,RS.[CurrencyId]  
        ,RS.[RestatementVersioncode]  
        ,RS.[SubsidiaryCode]  
        ,RS.[ChannelCode]  
        ,RS.[ProductFamilyCode]  
        ,RS.[ReportingPricingLevelId]  
        ,RS.[DataSourceCode]  
        ,RS.[ReportedOrganizationID]  
        ,RS.[CustomerGroupingCode]  
        ,RS.[RevenueStreamCode]  
        ,RS.[SubSegmentID]  
        ,RS.[LicenseTypeCode]  
        ,RS.[CurrencyCode]  
        ,RS.[Licenses]  
        ,RS.[BilledAmount]  
        ,RS.[BilledAmountLC]  
        ,RS.[BilledAmountCD]  
        ,RS.[AdjustedAmount]  
        ,RS.[AdjustedAmountLC]  
        ,RS.[AdjustedAmountCD]  
        ,RS.[BudgetCOGS]  
        ,RS.[BudgetCOGSLC]  
        ,RS.[BudgetCOGSCD]  
        ,0 AS ISdeleted  
   
       FROM     
        [Outbound].RollUpMSSalesBudgetRevenueSellIn  RS  
        LEFT JOIN [Outbound].MSSalesBudgetRevenueSellIn MSS   
         ON RS.[FiscalMonthId] = MSS.[FiscalMonthId]    AND  
             RS.[OriginalBillingMonthId] = MSS.[OriginalBillingMonthId] AND  
          RS.[RestatementVersionID]= MSS.[RestatementVersionID] AND  
          RS.[SubsidiaryID]= MSS.[SubsidiaryID]     AND  
          RS.[SalesTeamId]= MSS.[SalesTeamId]     AND  
          RS.[ChannelID]=MSS.[ChannelID]       AND  
          RS.[ProductFamilyID]= MSS.[ProductFamilyID]   AND  
          RS.[DataSourceID]= MSS.[DataSourceID]     AND  
          ISNULL(RS.[CustomerGroupingId],0) =ISNULL(MSS.[CustomerGroupingId],0) AND   
          RS.[RevenueStreamID] = MSS.[RevenueStreamID]   AND  
          RS.[LicenseTypeID]= MSS.[LicenseTypeID]     AND  
          RS.[BundleStatusId]= MSS.[BundleStatusId]   AND     
          RS.[CurrencyID]= MSS.[CurrencyID]     AND  
          RS.[ReportingPricingLevelId]= MSS.[ReportingPricingLevelId] AND  
          RS.[ReportedOrganizationID] = MSS.[ReportedOrganizationID] AND  
          RS.[SubsegmentId]=  MSS.[SubsegmentId]   
            
       WHERE  
        MSS.FiscalMonthId IS NULL  
  
       SELECT @RowCount = @@ROWCOUNT  
        
   /* SET IDENTITY_INSERT to ON.  */  
  SET IDENTITY_INSERT Tempdb. #TempExtractTableSellIn ON   
  
         /*INSERT Values from the base table if already exists   */  
            
       INSERT INTO #TempExtractTableSellIn WITH (TABLOCK)  
       (  
        [PlanCastTransactionID] ,  
        [FiscalMonthId],  
        [OriginalBillingMonthId],  
        [RestatementVersionID],  
        [SubsidiaryId],  
        [SalesTeamId],  
        [ChannelID],  
        [ProductFamilyID],  
        [DataSourceId],  
        [CustomerGroupingId],  
        [RevenueStreamID],  
        [LicenseTypeId],  
        [BundleStatusID],  
        [CurrencyId],  
        [RestatementVersioncode],  
        [SubsidiaryCode],  
        [ChannelCode],  
        [ProductFamilyCode],  
        [ReportingPricingLevelId],  
        [DataSourceCode],  
        [ReportedOrganizationID],  
        [CustomerGroupingCode] ,  
        [RevenueStreamCode],  
        [SubSegmentID],  
        [LicenseTypeCode],  
        [CurrencyCode],  
        [Licenses] ,  
        [BilledAmount],  
        [BilledAmountLC],  
        [BilledAmountCD],  
        [AdjustedAmount] ,  
        [AdjustedAmountLC],  
        [AdjustedAmountCD] ,  
        [BudgetCOGS] ,  
        [BudgetCOGSLC] ,  
        [BudgetCOGSCD],  
        [ISDeleted]   
       )  
       SELECT   
        MSS.[PlanCastTransactionID]  
        ,ISNULL(RS.[FiscalMonthId] ,MSS.[FiscalMonthId])  
        ,ISNULL(RS.[OriginalBillingMonthId] ,MSS.[OriginalBillingMonthId])  
             ,ISNULL(RS.[RestatementVersionID],MSS.[RestatementVersionID])  
            ,ISNULL(RS.[SubsidiaryId],MSS.[SubsidiaryId])  
        ,ISNULL(RS.[SalesTeamId],MSS.[SalesTeamId])  
            ,ISNULL(RS.[ChannelID],MSS.[ChannelID])  
               ,ISNULL(RS.[ProductFamilyID],MSS.[ProductFamilyID])  
                      ,ISNULL(RS.[DataSourceId], MSS.[DataSourceId])  
                      ,ISNULL(ISNULL(RS.[CustomerGroupingId],MSS.[CustomerGroupingId]),0)  
                      ,ISNULL(RS.[RevenueStreamID],MSS.[RevenueStreamID])  
                       ,ISNULL(RS.[LicenseTypeId],MSS.[LicenseTypeId])  
                     ,ISNULL(RS.[BundleStatusID], MSS.[BundleStatusID])  
                     ,ISNULL(RS.[CurrencyId],MSS.[CurrencyId])  
                     ,ISNULL(RS.[RestatementVersioncode],MSS.[RestatementVersioncode])  
                      ,ISNULL(RS.[SubsidiaryCode],MSS.[SubsidiaryCode])  
                      ,ISNULL(RS.[ChannelCode],MSS.[ChannelCode])  
                     ,ISNULL(RS.[ProductFamilyCode],MSS.[ProductFamilyCode])  
                       ,ISNULL(RS.[ReportingPricingLevelId],MSS.[ReportingPricingLevelId])  
                      ,ISNULL(RS.[DataSourceCode],MSS.[DataSourceCode])  
                     ,ISNULL(RS.[ReportedOrganizationID],MSS.[ReportedOrganizationID])  
                      ,ISNULL(RS.[CustomerGroupingCode],MSS.[CustomerGroupingCode])  
                     ,ISNULL(RS.[RevenueStreamCode],MSS.[RevenueStreamCode])  
                    ,ISNULL(RS.[SubSegmentID],MSS.[SubSegmentID])  
                     ,ISNULL(RS.[LicenseTypeCode], MSS.[LicenseTypeCode])  
                    ,ISNULL(RS.[CurrencyCode],MSS.[CurrencyCode])  
                    ,ISNULL(RS.[Licenses],MSS.[Licenses])  
                       ,ISNULL(RS.[BilledAmount],MSS.[BilledAmount])  
                    ,ISNULL(RS.[BilledAmountLC],MSS.[BilledAmountLC])  
                     ,ISNULL(RS.[BilledAmountCD],MSS.[BilledAmountCD])  
                       ,ISNULL(RS.[AdjustedAmount],MSS.[AdjustedAmount])  
                     ,ISNULL(RS.[AdjustedAmountLC],MSS.[AdjustedAmountLC])  
                       ,ISNULL(RS.[AdjustedAmountCD],MSS.[AdjustedAmountCD])  
                    ,ISNULL(RS.[BudgetCOGS],MSS.[BudgetCOGS])  
                     ,ISNULL(RS.[BudgetCOGSLC],MSS.[BudgetCOGSLC])  
                     ,ISNULL(RS.[BudgetCOGSCD], MSS.[BudgetCOGSCD])  
                    ,CASE WHEN RS.FiscalMonthId IS NULL THEN   1 ELSE  0 END AS ISdeleted   
       FROM     
        [Outbound].RollUpMSSalesBudgetRevenueSellIn  RS  
        RIGHT JOIN [Outbound].MSSalesBudgetRevenueSellIn MSS   
         ON RS.[FiscalMonthId] = MSS.[FiscalMonthId] AND  
          RS.[OriginalBillingMonthID] = MSS.[OriginalBillingMonthID] AND  
            RS.[RestatementVersionID]= MSS.[RestatementVersionID] AND  
          RS.[SubsidiaryID]= MSS.[SubsidiaryID] AND  
          RS.[SalesTeamID]= MSS.[SalesTeamID] AND  
          RS.[ChannelID]=MSS.[ChannelID] AND  
          RS.[ProductFamilyID]= MSS.[ProductFamilyID] AND  
          RS.[DataSourceID]= MSS.[DataSourceID] AND  
          ISNULL(RS.[CustomerGroupingId],0) =ISNULL(MSS.[CustomerGroupingId],0) AND   
          RS.[RevenueStreamID] = MSS.[RevenueStreamID]  AND  
          RS.[LicenseTypeID]= MSS.[LicenseTypeID] AND  
          RS.[BundleStatusId]= MSS.[BundleStatusId] AND     
          RS.[CurrencyID]= MSS.[CurrencyID] AND  
          RS.[ReportingPricingLevelId]= MSS.[ReportingPricingLevelId] AND  
          RS.[ReportedOrganizationID] = MSS.[ReportedOrganizationID] AND  
          RS.[SubsegmentId]=  MSS.[SubsegmentId]  
        WHERE             
         (ISNULL(RS.[Licenses],0) <>ISNULL(MSS.[Licenses],0)  
         OR ISNULL(RS.[BilledAmount],0)<>ISNULL(MSS.[BilledAmount],0)  
         OR ISNULL(RS.[BilledAmountLC],0)<>ISNULL(MSS.[BilledAmountLC],0)  
         OR ISNULL(RS.[BilledAmountCD],0)<>ISNULL(MSS.[BilledAmountCD],0)  
         OR ISNULL(RS.[AdjustedAmount],0)<>ISNULL(MSS.[AdjustedAmount],0)  
         OR ISNULL(RS.[AdjustedAmountLC],0)<> ISNULL(MSS.[AdjustedAmountLC],0)  
         OR ISNULL(RS.[AdjustedAmountCD],0)<>ISNULL(MSS.[AdjustedAmountCD],0)  
         OR ISNULL(RS.[BudgetCOGS],0)<>ISNULL(MSS.[BudgetCOGS],0)  
         OR ISNULL(RS.[BudgetCOGSLC],0)<>ISNULL(MSS.[BudgetCOGSLC],0)  
         OR ISNULL(RS.[BudgetCOGSCD],0)<> ISNULL(MSS.[BudgetCOGSCD],0))  
         OR RS.FiscalMonthId IS NULL  
                                              
        /* SET IDENTITY_INSERT to OFF.  */  
       SET IDENTITY_INSERT Tempdb. #TempExtractTableSellIn OFF   
            
       SELECT @RowCount =@RowCount +@@ROWCOUNT   
            
                         -----------------------Store PROCESSHISTORY Start-----------------------------  
       SELECT @Action = 'Insert',@Mode = '',@Object ='#TempExtractTableSellIn',@ObjectType = 'T'  
                 ,@Rows =@RowCount ,@Status = 'END'  
                 ,@LogMsg = 'Inserted Delta data into Temp table:Tempdb.#TempExtractTableSellIn for '+@ExtractSellIn    
                 ,@TaskName = NULL        
                             
       EXECUTE Staging.StoreProcessHistory   
          @Process = @SP  
          ,@Status  = @Status  
                  ,@Mode    = @Mode    
                      ,@Object  = @Object  
                                        ,@ObjectType = @ObjectType  
                                        ,@Action = @Action  
                                        ,@Comment = @LogMsg  
                                        ,@Debug   = @Debug  
                                        ,@TaskName = @TaskName  
                                        ,@WorkFlowId = @WorkFlowId  
                                        ,@ParentTaskId = @TaskId   
                                        ,@RunId = @RunId  
                      -----------------------Store PROCESSHISTORY END-----------------------------   
                                  
  
  
  
  
            
             
    
       /*The Extract table is truncated and reloaded every time a new extract is created and the Most recent iteration can be found in  
       'PriorIterationExtract%' tables*/  
            
            
       TRUNCATE TABLE [Outbound].ExtractMSSalesBudgetRevenueSellIn  
             
               -----------------------Store PROCESSHISTORY Start-----------------------------  
        SELECT @Action = 'Insert',@Mode = '',@Object = 'ExtractMSSalesBudgetRevenueSellIn',@ObjectType = 'T'  
           ,@Rows = NULL,@Status = 'Start'  
                                            ,@LogMsg = 'Starting insertion of records from Temp table to Extract table: '+@ExtractSellIn   , @TaskName = NULL   
                                                                    
       EXECUTE Staging.StoreProcessHistory   
              @Process = @SP  
              ,@Status  = @Status  
              ,@Mode    = @Mode    
              ,@Object  = @Object  
              ,@ObjectType = @ObjectType  
              ,@Action = @Action  
              ,@Comment = @LogMsg  
              ,@Debug   = @Debug  
              ,@TaskName = @TaskName  
              ,@WorkFlowId = @WorkFlowId  
              ,@ParentTaskId = @TaskId   
              ,@RunId = @RunId  
               -----------------------Store PROCESSHISTORY END-----------------------------  
                    
       INSERT INTO  [Outbound].ExtractMSSalesBudgetRevenueSellIn WITH (TABLOCK)  
        ([IterationID]  
                     ,[PlanCastTransactionID]   
                     ,[FiscalMonthId]  
        ,[OriginalBillingMonthID]  
                     ,[RestatementVersionID]  
                     ,[SubsidiaryId]  
        ,[SalesTeamId]  
                          ,[ChannelID]  
                          ,[ProductFamilyID]  
                            ,[DataSourceId]  
                           ,[CustomerGroupingId]  
                          ,[RevenueStreamID]  
                          ,[LicenseTypeId]  
                           ,[BundleStatusID]  
                           ,[CurrencyID]  
                           ,[RestatementVersioncode]  
                             ,[SubsidiaryCode]  
                                ,[ChannelCode]  
                                ,[ProductFamilyCode]  
                                ,[ReportingPricingLevelId]  
                                ,[DataSourceCode]  
                                ,[ReportedOrganizationID]  
                                ,[CustomerGroupingCode]  
                                ,[RevenueStreamCode]  
                                ,[SubSegmentID]  
                                ,[LicenseTypeCode]  
                                ,[CurrencyCode]  
                                ,[Licenses]  
                                ,[BilledAmount]  
                                ,[BilledAmountLC]  
                                ,[BilledAmountCD]  
                                ,[AdjustedAmount]  
                                ,[AdjustedAmountLC]  
                                ,[AdjustedAmountCD]  
                                ,[BudgetCOGS]  
                                ,[BudgetCOGSLC]  
                                ,[BudgetCOGSCD]  
                                ,[IsDeleted])  
       SELECT   
                                    @IterationID  
                                ,[PlanCastTransactionID]   
                                ,[FiscalMonthId]  
        ,[OriginalBillingMonthID]  
                                ,[RestatementVersionID]  
                                ,[SubsidiaryId]  
        ,[SalesTeamId]  
                                ,[ChannelID]  
                                ,[ProductFamilyID]  
                                ,[DataSourceId]  
                                ,[CustomerGroupingId]  
                                ,[RevenueStreamID]  
                                ,[LicenseTypeId]  
                                ,[BundleStatusID]  
                                ,[CurrencyID]  
                                ,[RestatementVersioncode]  
                                ,[SubsidiaryCode]  
                                ,[ChannelCode]  
                                ,[ProductFamilyCode]  
                                ,[ReportingPricingLevelId]  
                                ,[DataSourceCode]  
                                ,[ReportedOrganizationID]  
                                ,[CustomerGroupingCode]  
                                ,[RevenueStreamCode]  
                                ,[SubSegmentID]  
                                ,[LicenseTypeCode]  
                                ,[CurrencyCode]  
                                ,[Licenses]  
                                ,[BilledAmount]  
                                ,[BilledAmountLC]  
                                ,[BilledAmountCD]  
                                ,[AdjustedAmount]  
                                ,[AdjustedAmountLC]  
                                ,[AdjustedAmountCD]  
                                ,[BudgetCOGS]  
                                ,[BudgetCOGSLC]  
                                ,[BudgetCOGSCD]  
                                ,[IsDeleted]   
       FROM #TempExtractTableSellIn

I am facing issue with DBCC checkident for above Stored Proc in Azure SQL. The error

User 'guest' does not have permission to run DBCC CHECKIDENT for object '#TempExtractTableSellIn"

sql-server

1条答案

按热度按时间
pftdvrlh

pftdvrlh1#

From doc: DBCC CHECKIDENT
Permissions
Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

I tried to run DBCC CHECKIDENT on the temporary table of SQL database master database and database.

  • When I query on SQL database, I created it got succeed.
  • When I query on master database it gave me error similar to you.

As you see in below image:

So, you can try it in SQL database you created to resolve it.

As per this SO Thread answerd by @Maximus alternative for DBCC CHECKIDENT as below.

SET IDENTITY_INSERT [MyTable] ON
INSERT INTO Mytable(ID,name)values (@AutoIncrementvalue-1,'siva')
SET IDENTITY_INSERT [MyTable] OFF
赞(0）分享  回复(0）举报  2023-11-16
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com