I am trying to run a stored procedure on the same database using two different login users. While using the Windows authentication , I'm able to run the stored and proc and see all the data available. But while running the same stored proc using a specific user authentication, I am not able to view the data. The stored proc is running but returning 0 rows.

The permissions for both accounts are the same.

Any suggestions on what might be the problem or how to approach in solving this issue?

  • What is the role specified at schema for that specific user authentication ? Could be possible that windows authentication has select/update/delete permissions as compared to that user auth and hence you see the diff in results? – KASQLDBA Jan 7 '16 at 18:51
  • Check the default database assigned to each login and/or make sure you are specifying the correct database when connecting. Do you have this stored procedure in more than one place? Did you by chance accidentally create a copy of it in Master? – datagod Jan 7 '16 at 19:51
  • 1
    I had that happen once and it turned out that the SP had code in it that did some user defined security. Is that possible in this case? – Kenneth Fisher Jan 7 '16 at 19:52
  • 1
    How are you connecting and then calling the stored procedure? Show some actual code please... – Aaron Bertrand Jan 7 '16 at 21:28

Your Answer

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

Browse other questions tagged or ask your own question.