0

I have a small app which calls a stored procedure over ODBC using a sysadmin user with login and password.

The problem is that some (Windows Domain) users get an error saying execute permissions were denied on the stored procedure, whilst other users do not get any such error. However, both use the same app which uses the same login and password. The app does not use Trusted Connection or Integrated Security at all, only login and password from an ini file that stores encrypted credentials.

I'm at a loss. I have run SQL Server Profiler whilst the program is being used to confirm the user executing the procedure is as expected, I have tried setting permissions up for Windows Domain users in the Database... I have no idea why it would work for some and not for others when none of it was dependent on windows user permissions. Not only that, but it was working fine for everyone last week.

Is there anywhere I can look, Or tests I can do, that might help me diagnose the problem?

EDIT: Unfortunately I don't have access to the source code.

  • 1
    Can you add the code SELECT USER just before the call of the proc in your app? – sepupic Feb 14 at 14:00
  • 1
    Is the stored procedure calling out to some other procedure that IS aware of the OS user, and maybe that secondary proc is where the execute dies? – CaM Feb 14 at 15:07
  • 1
    You can also configure a server audit with a database audit specification, restricted to the relevant procedure and capture the successful as well as failed event, and based on that see what might differ between those audit records. – Tibor Karaszi Feb 14 at 16:57
0

I found the issue through screenshare with a user. The ODBC DSNs on some client machines were set up to use Windows Authentication, and the ODBC implementation in the applications was rather simplistic, so when running the application it was using our Windows logins. It worked for me since I am sysadmin on the database server.

The ODBC DSN setup has been changed to use SQL Authentication with a username and password, and now it works.

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.