SQL Server Performance Forum – Threads Archive

Permissions Behavior

Hi,
We are observing strange behaviour in our application. The application’s installation setup creates database and its objects using sql scripts. Appropriate permission on all the Stored Procs are assigned using "Grant Exec ..". When a User who has appropriate permission first use the application, the application gives an error message for a
specific stored proc. "Execute permission denied on object usp_xyz …". Then to verify the permission we double click on it and confirms that permissions are shown absolutely fine. Now just Click either on Cancel or OK and use the application, it starts to work properly. Thereafter we that issue doesn’t appear. This issue comes with that particular Stored Proc and only during first use after installation on a new machine; then double clicking that SP resolves the issue. We are using SQL server 2000 SP4. Any idea ? Thanks

It sounds very odd. I would run profiler and include the logon and execute events. Make sure that the application is using the login it should be.

Thanks Simon. Yes it seems very odd to me either. Let me check the things with profiler and let you know the results.

May check this KBAhttp://support.microsoft.com/kb/819829 for relevant hotfix, not sure whether this is addressed or not. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Make sure the calls to the SP include the sp’s owner prefix, like EXEC dbo.usp_xyz.

]]>