Unanswered: Prevent user from Running Stored proc in Query Analyzer
Is there a way to prevent users from executing the stored procedure in query analyzer.
The access to live system is given thru a specific role and each user is bound to that role.Now thru the application we are controling the Select/Insert/Update/Delete, but at the back end the S/I/U/D are getting done thru the role.
So in reality even if we prevent users from I/U/D thru the application, they can still execute the Delete stored proc thru the Query analyzer.
Re: Prevent user from Running Stored proc in Query Analyzer
Consider better using security schema. If user can run SP, he can.
Apply triggering code to track his activities. Deny access to these audit tables to public. Remove users from db_owner role and similar ones if you use it.
select case when 'SQL Query Analyzer'= (select program_name from master.dbo.sysprocesses (nolock) where spid=@@spid) then 1 else 0 end