October 30, 2018 at 9:58 am
Hi, I have a colleague we cannot execute an SP they get an error saying: User does not have permission to perform this action.
I can run it fine but I am a SYS admin. I have checked there permissions and they have execute permissions and read access to the database.
Can anyone advise me a way of checking which part of the SP is failing when they run it?
October 30, 2018 at 10:04 am
Check to make sure the user is running the procedure from the correct database context ( I have seen a number of people default to Master, and not find things there).
Second would be to check to procedure for actions that may be out of the user's ability to run (truncate table, Dynamic SQL, etc.)
October 30, 2018 at 10:13 am
crow1969 - Tuesday, October 30, 2018 10:04 AMCheck to make sure the user is running the procedure from the correct database context ( I have seen a number of people default to Master, and not find things there).Second would be to check to procedure for actions that may be out of the user's ability to run (truncate table, Dynamic SQL, etc.)
Unless it has dynamic SQL in it you should be able to run all the commands in the SP.
Make sure you have granted the user EXECUTE permissions on the stored procedure:GRANT EXECUTE ON [dbo].[mySP] TO myUser;
October 30, 2018 at 1:24 pm
The ability to run the commands in the procedure gets a little tricky. Things like TRUNCATE TABLE, and other DDL statements require special permissions at least on a schema level.
Things like update, inserts, or deletes on a table can be encapsulated in a procedure, as long as the chain of ownership from the procedure to the table is unbroken. That way, a user can have read-only permissions, but still be able to update a table (i.e. only through the procedure).
November 19, 2018 at 12:36 am
You have to check if your SP need to access to different databases too: in that case your user must have grants in all databases accessed by the SP
(and thi s explains why sysadmins can run it without problems)
November 22, 2018 at 1:32 pm
I can run it fine but I am a SYS admin. I have checked there permissions and they have execute permissions and read access to the database.
Can anyone advise me a way of checking which part of the SP is failing when they run
Probably you will have to modify the stored procedure to include EXECUTE AS DBO inside the Stored procedure.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply