May 9, 2012 at 8:27 am
In running an app in a test environment that used to work, I am now seeing the error "The EXECUTE permission was denied on the object 'uspBlahBlah'....". It fails on the first attempt to hit the db, I'm quite sure it's a global permissions problem with sp access and that login (and not a problem specific to that stored proc).
In looking the the security properties of the login, the login is mapped to the proper database and is a member of db_datareader and db_datawriter. This is how we have always had it configured, and how it has successfully been running forever.
Does anyone have ideas of why execute permissions may have been lost to stored procs? (I've seen before where permissions don't stick, so I already dropped the user from the db and re-added it, but no luck).
May 9, 2012 at 8:36 am
if someone, say a developer, had a change to the code, and did a DROP PROCEDURE usp_blahBlah and then did CREATE PROCEDURE usp_BlahBlah, the permissiosn will be lost.
if the develoepr did an ALTER PROCEDURE usp_BlahBlah instead , the permissions would remain in place, but the code would get updated.
pretty sure that's what happened in that case.
you could check that by selecting create_date,modify_date from sys.objects where name = 'usp_BlahBlah' to see when it changed, and you coudl go to the default trace to see who did it (unless everyone logs in as sa!)
Lowell
May 9, 2012 at 8:37 am
I have never seen a case where permissions fail to 'stick' without someone or something intervening.
Having said that, being a member of the datareader/datawriter roles does not give execute permissions on any stored procedures. Execute must be granted manually to each procedure.
Maybe the user was a member of db_owner role as well, in which case execution permissions is inherent.
The probability of survival is inversely proportional to the angle of arrival.
May 9, 2012 at 8:41 am
you might also consider just granting execute to stored procedures on the database.
USE your_database
go
CREATE ROLE db_executor
USE your_database
go
GRANT EXECUTE TO db_executor
that way, whenever a Dev or someone drops a procedure and then recreates it, you do not need to worry about granting execute to individual stored procedures. they will have read, write and execute permissions.
May 9, 2012 at 9:14 am
On production (which works), when I view permissions for the given user/database, Explicit tab, I see Grant checked for
Connect
Execute
Insert
Select
Update
The Effective tab shows all of above plus Delete.
The login/user is only a member of db_datareader and db_datawriter, so I'm guessing at some point someone checked the 'Execute' box?
If the user was dropped and re-added in my test environment (which can't execute sps), that Execute permission would not have been created. Does this sound correct?
May 23, 2018 at 7:37 am
Geoff A - Wednesday, May 9, 2012 8:41 AMyou might also consider just granting execute to stored procedures on the database. USE your_databasegoCREATE ROLE db_executorUSE your_databasegoGRANT EXECUTE TO db_executorthat way, whenever a Dev or someone drops a procedure and then recreates it, you do not need to worry about granting execute to individual stored procedures. they will have read, write and execute permissions.
I did this. But still missing access to the few Sp's
-- Create a db_executor role
CREATE ROLE db_executor-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor
May 23, 2018 at 7:45 am
rajasekhar.bollareddy - Wednesday, May 23, 2018 7:37 AMGeoff A - Wednesday, May 9, 2012 8:41 AMyou might also consider just granting execute to stored procedures on the database. USE your_databasegoCREATE ROLE db_executorUSE your_databasegoGRANT EXECUTE TO db_executorthat way, whenever a Dev or someone drops a procedure and then recreates it, you do not need to worry about granting execute to individual stored procedures. they will have read, write and execute permissions.
I did this. But still missing access to the few Sp's
-- Create a db_executor role
CREATE ROLE db_executor-- Grant execute rights to the new role
GRANT EXECUTE TO db_executor
That isn't what Geoff wrong 6 years ago??? You might want to reformat your reply.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply