SQL PErmiisions using MS Access as front end
Posted on 2011-03-12
I'm trying to clean up some databases I have inherited and am confused by some things I see happening. We have several databases, all on an Active Directory domain. The previous administrator set up permissions to the databases based on the users AD roles. So for the most part all the Users in SQL Security are AD Roles, not individual users. That seems to be a pretty smart way of doing things but as I look around I see that many of the Roles are added to db_Owner. I'm thinking this was done because he couldn't figure out how to grant granular permissions so for a quick fix, just added the roled to db_Owner and never got around to fixing the underlying issue.
I removed one group, Trainers, from db_Owner and granted them permissions to execute a particular stored procedure just to test and I get a permission denied when I try to execute the sproc in Access using my test account which belongs to Trainers AD group. I went back and added Trainers to the db_dataReader and db_dataWriter roles and now they can execute the update sproc.
OK, so my question is, do all users need to be in the db_datawriter role if I want them to be able to update records, even though I grant them Execute rights in the update sproc?