I am in the process of refactoring legacy system which relies on huge a stored procedure with large calling tree of dependent stored procedures/UDFs and a lot of interconnected linked servers.

Production stored procedure runs fine while test one fails with

Login failed for user 'user_test'

I can get no further details from error_procedure() and error_line().

Complete output:

(1 row affected)

(1 row affected)


ErrorNumber=[18456]; ErrorSeverity=[14]; ErrorState=[1]; ErrorProcedure=[]; ErrorLine=[1]; ErrorMessage=[Login failed for user 'user_test'.]

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

Apparently some of the linked servers are not properly setup on the test environment. So I would need to either set them up or mock/comment external calls. I cannot debug the stored procedure on a test server.

How to find exact line in the code which throws this error without adding traces after each line of SQL?

System runs in SQL Server 2016 on Windows Server 2012 R2.

  • 1
    You could add some PRINT statements to add more verbose output to the messages pane; that would help you narrow it down. – Larnu May 30 '19 at 16:49
  • It would be the last resort as there are 10000+ lines of SQL in total. – Anton Krouglov May 30 '19 at 17:01
  • Does your test have user called user_test? – Eric May 30 '19 at 17:15
  • Yes, it does. There is an SQL security set up. user_test is the name of both sql login and database user. – Anton Krouglov May 30 '19 at 17:19
  • I agree with @Larnu - cleverly placed Print statements will help you zero in on the problem line(s) in a few executions. Starting with 10K lines, in four iterations you could have the range narrowed down to 600 lines. It's not elegant, but it'll get the job done. – Brian May 30 '19 at 21:22

Try wrapping entire body of your proc into a try/catch block and throw from catch block, instead of relying on built in error functions.

you can also try to find linked server definition that has user_test login specified and try to check if it works. granted you may have to do it on all servers in the stack.

Select * 
from sys.servers 
    inner join sys.linked_logins on servers.server_id = linked_logins.server_id
    remote_name = 'user_test'
  • That is done already. Output ErrorNumber=[18456]; ErrorSeverity=[14]; ErrorState=[1]; ErrorProcedure=[]; ErrorLine=[1]; ErrorMessage=[Login failed for user 'user_test'] – Anton Krouglov May 30 '19 at 16:59
  • try finding a broken linked server that is using the credentials returned by the error – Daniel N May 30 '19 at 17:16
  • Well, all of them are[should be] broken. I cannot just figure our which nested SP/UDF calls any of them. – Anton Krouglov May 30 '19 at 17:21
  • try looking for a reference in syscomments.text, check for either 3 part name reference, linkedserver names, openrowset, opendatasource or openquery functions – Daniel N May 30 '19 at 17:31
  • There are hundreds of references there – Anton Krouglov May 30 '19 at 17:32

SQL Profiler can capture the individual statements in a stored procedure. See details here. Using such capture I have identified statement preceding an error. Then I have searched through SQL code using Toad. Problem was pinpointed. It was something like: exec sp_executesql 'select top 1 * from openquery(ext_server, ...

If there exists more elegant solution to this - please post an answer here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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