0

I run into a weird bug?

User complained to me that he cannot anymore update certain tables in database. He had owner rights in database and the login was window login (ad-login). I tried elevating him to sysadm, checked all the rights and even deleted and added the login again. Still the problem continued.

I tried creating a SQL Server sysadm-login, so I wouldn't have to think about AD-issue and tried that. the same error consists. Both of the logins can login to SQL-instance, SELECT data, UPDATE certain tables but cannot UPDATE other ones.

Older logins (like mine) work fine on evertyhing, but the newer ones don't. I have no idea what could be causing this.

Msg 18456, Level 14, State 1, Line 1 Login failed for user 'xxxx'

Version: Microsoft SQL Server 2017 (RTM-CU31) (KB5016884) - 14.0.3456.2 (X64) Sep 2 2022 11:01:50 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: ) (Hypervisor)

13
  • I don't know could for example corruption/fragmentation in the sys. tables cause this? I checked the logins ffrom sys tables and they seem fine Aug 11, 2023 at 11:50
  • What does the SQL Logs say for the specific reason for the login failure?
    – J.D.
    Aug 11, 2023 at 12:01
  • Msg 18456, Level 14, State 1, Line 1 Login failed for user 'xxxx' this is all I get Aug 11, 2023 at 12:02
  • And the "failed" login doesn't even show on logs Aug 11, 2023 at 12:02
  • 3
    Check if the table you are going to update is 'regular' table and not synonym to remote one via linked server
    – SergeyA
    Aug 11, 2023 at 15:23

2 Answers 2

1

This answer just desribe how I investigate the root cause/causes in these problems:

  1. Using SQLProfiler define a trace having following events: Error Log, Event Log, Exception, User Error Message, RPC:Starting, SQL:BatchStarting.
  2. Execute and wait to receive errors and check with RPC:Starting, SQL:BatchStarting events.

Basically if you succed to find this error using this SQLProfiler session, you have to search the RPC:Starting, SQL:BatchStarting events which are before error, events having same sesion_id (which is the SPID col). Check this picture for SPID col enter image description here

0

Check if the table you are going to update is 'regular' table and not synonym to remote one via linked server – SergeyA

This was the problem. What I did was take a clone of the WM and span it up, without network access. Immeadiately running the query I got an error msg

DB provider "SQLNCLI11" for linked server "XXX" returned message....

So indeed thete is some relation to a linked server and the login is missing rights there. Apparently the failed logins were logged there, so that explains why there are no errors on logs on the server that I tried to debug.

Thank you very much!!!

This site is temporarily in read-only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .