Troubleshhoting User''s permissions

Question

Troubleshhoting User''s permissions

Ernest Moody-418955

SSC Journeyman

Points: 94
More actions
March 15, 2007 at 7:21 am

#119186

I am having a problem with an application using MS Access project/SQL.
Developed a delete record function where Access runs three stored procedures to delete a record and any associated records for the individual. The first procedure flags any associated records, the second deletes the flagged associated records and the third deletes the main record.
Functionality works for the dbo, myself, but is non-functional for the users. Users click the command button and nothing happens.
Users have all permissions on the data tables except DRI and execute for the stored procedures.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike Good SSCertifiable Points: 7415 More actions · Answer 1

Not much here to go on. As long as your users have exec rights on the procs, they shouldn't even need permissions on the tables.

Debugging suggestions: 1) In SSMS query window or Query Analyzer, run the procs under your users' security context, see what happens. One way is to use "EXECUTE as LOGIN" syntax to simulate your user's login context--BOL covers this pretty well. 2) run SQLProfiler to verify that something is happening when user clicks your button. If you don't see anything with the default (Standard) profile, try adding the SP:Completed event.

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 2

also, check if the click handler doesn't swallow exceptions. it's unbelievably common to have empty catch block.

Piotr

...and your only reply is slàinte mhath

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 3

Mike Good (3/14/2008)
Not much here to go on. As long as your users have exec rights on the procs, they shouldn't even need permissions on the tables.

Unless you are dealing with dynamic SQL inside a stored procedure. In that case you do need explicit permissions on the tables. Dynamic SQL operates outside the normal execution procedure of the stored procedure hosting it.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]