Stored procedure

  • Hi,

    One of the user does not have access to execute a sql stored procedure but when he run the sp it says completed successfully. SP is delete to some rows and rows are not deleted actually but in management studio it says executed successfully.

    SQL should say that user dont have access why it says completed even though work is not done? any ideas?

  • If the user is able to execute the procedure without error, then that user has access to the stored procedure. Maybe the security is internal and just preventing the delete statement and exiting cleanly.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • He may be part of a group/role that does have access. You have to start with the execution rights on the object and work backwards. Also note that DBO and SA roles can't be blocked without exclusive DENYs.

    How is he calling the sp? Is he running it from SSMS or is there a front end that's firing the proc off? If it's the front end, is there error trapping that could be hiding a GRANT issue by not accounting for it?


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

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

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