SQL Maintenance Plan Wizard - Schedule Reorganize Index (Login Failed for user error)

Chua Liang Wei 186 Reputation points
2022-05-13T09:42:34.507+00:00

SQL Maintenance Plan Wizard - Schedule Reorganize Index (Login Failed for user error). How can we fix the error as we do not have issue using the Id to login and it has sysadmin right.

201716-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,676 questions
0 comments

7 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,736 Reputation points
    2022-05-13T09:46:09.137+00:00

    Login Failed for user error

    And what's not clear on the error message?
    Check if the SQL login exists and have the permissions to execute the job with all it's tasks.

    1 person found this answer helpful.
    0 comments
  2. Chua Liang Wei 186 Reputation points
    2022-05-13T10:09:53.647+00:00

    the login is exist with sysadmin right. The permission level no sufficient? Where can we check which login has permission to execute schedule job.

  3. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2022-05-16T10:02:12.23+00:00

    Hi @Chua Liang Wei ,

    Any update for this thread? Did you read your SQL server error log? If you get any other related error message, please share us the information. This maintenance plan run under SQL agent service account? Which account that running SQL agent service? Did the account is a sysadmin account or has permission to execute this job? To execute ALTER INDEX, at a minimum, ALTER permission on the table or view is required.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments
  4. CathyJi-MSFT 21,086 Reputation points Microsoft Vendor
    2022-05-18T09:43:11.927+00:00

    Hi @Chua Liang Wei ,

    >Executed as user: NT Service\SQLSERVERAGENT.

    This job executed as SQL server agent service account. So suggest you to change your SQL server agent service account to admin account. Or an account that has ALTER permission on the table or view.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

  5. Chua Liang Wei 186 Reputation points
    2022-05-18T03:32:34.623+00:00

    This the error grab from the job history

    Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted
    05/13/2022 00:00:00,EPM Reindexing.Subplan_1,Error,,<DB>,EPM Reindexing.Subplan_1,,,The job failed. The Job was invoked by Schedule 10 (EPM Reindexing). The last step to run was step 1 (Subplan_1).,00:00:03,0,0,,,,0
    05/13/2022 00:00:00,EPM Reindexing.Subplan_1,Error,1,<DB>,EPM Reindexing.Subplan_1,Subplan_1,,Executed as user: NT Service\SQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 13.0.5026.0 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 12:00:00 AM Error: 2022-05-13 00:00:02.59 Code: 0xC00291EC Source: {FD7724A7-9959-45DA-B972-7C82B0073AF4} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2022-05-13 00:00:02.59 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2022-05-13 00:00:02.69 Code: 0xC0024104 Source: Reorganize Index Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'svradm'.). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Error: 2022-05-13 00:00:02.72 Code: 0xC0024104 Source: {EC71C8C1-C082-4FC4-A966-841D1A254BB4} Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'svradm'.). The Execute method must succeed<c/> and indicate the result using an "out" parameter. End Error Warning: 2022-05-13 00:00:02.72 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded<c/> but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 12:00:00 AM Finished: 12:00:02 AM Elapsed: 2.391 seconds. The package execution failed. The step failed.,00:00:02,0,0,,,,0
    05/12/2022 15:59:39,EPM Rebuild Index.Subplan_1,Error,,<DB>,EPM Rebuild Index.Subplan_1,,,The job failed. The Job was invoked by User svradm. The last step to run was step 1 (Subplan_1).,00:36:29,0,0,,,,0
    05/12/2022 15:59:39,EPM Rebuild Index.Subplan_1,Error,1,<DB>,EPM Rebuild Index.Subplan_1,Subplan_1,,Executed as user: NT Service\SQLSERVERAGENT. Started: 3:59:39 PM Finished: 4:36:06 PM Elapsed: 2186.7 seconds. The package execution failed. The step failed.,00:36:29,0,0,,,,0
    05/12/2022 15:40:04,EPM Reindexing.Subplan_1,Error,,<DB>,EPM Reindexing.Subplan_1,,,The job failed. The Job was invoked by User svradm. The last step to run was step 1 (Subplan_1).,00:56:04,0,0,,,,0
    05/12/2022 15:40:04,EPM Reindexing.Subplan_1,Error,1,<DB>,EPM Reindexing.Subplan_1,Subplan_1,,Executed as user: NT Service\SQLSERVERAGENT. Started: 3:40:04 PM Finished: 4:36:06 PM Elapsed: 3361.94 seconds. The package execution failed. The step failed.,00:56:04,0,0,,,,0

    0 comments