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.
    – Thom A
    May 30, 2019 at 16:49
  • It would be the last resort as there are 10000+ lines of SQL in total. May 30, 2019 at 17:01
  • Does your test have user called user_test?
    – Eric
    May 30, 2019 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. May 30, 2019 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, 2019 at 21:22

2 Answers 2


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.


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'] May 30, 2019 at 16:59
  • try finding a broken linked server that is using the credentials returned by the error
    – Daniel N
    May 30, 2019 at 17:16
  • Well, all of them are[should be] broken. I cannot just figure our which nested SP/UDF calls any of them. May 30, 2019 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, 2019 at 17:31
  • There are hundreds of references there May 30, 2019 at 17:32

Your Answer

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

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