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)

Improve this question
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
    – user1054844
    Aug 11 at 11:50
  • What does the SQL Logs say for the specific reason for the login failure?
    – J.D.
    Aug 11 at 12:01
  • Msg 18456, Level 14, State 1, Line 1 Login failed for user 'xxxx' this is all I get
    – user1054844
    Aug 11 at 12:02
  • And the "failed" login doesn't even show on logs
    – user1054844
    Aug 11 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 at 15:23

2 Answers 2

Reset to default
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

Improve this answer
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!!!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.