[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 398
  • Last Modified:

SQL PErmiisions using MS Access as front end

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?
0
AkAlan
Asked:
AkAlan
  • 3
1 Solution
 
Jerry MillerCommented:
Yes db_writer is needed to update records.

Here is a list of pre-defined db roles in SQL from TechRepublic:
http://www.techrepublic.com/article/understanding-roles-in-sql-server-security/1061781

    * db_owner: Members have full access.
    * db_accessadmin: Members can manage Windows groups and SQL Server logins.
    * db_datareader: Members can read all data.
    * db_datawriter: Members can add, delete, or modify data in the tables.
    * db_ddladmin: Members can run dynamic-link library (DLL) statements.
    * db_securityadmin: Members can modify role membership and manage permissions.
    * db_bckupoperator: Members can back up the database.
    * db_denydatareader: Members can’t view data within the database.
    * db_denydatawriter: Members can’t change or delete data in tables or views.
0
 
AkAlanAuthor Commented:
OK, I added my test AD user account to the Trainers AD group, added Trainers to the  db_datawriter role and I have an Access 2007 form that has a stored Procedure for it's recordsource. I get the message "This recordset is not updateable" when I try and type in a text box. I added Trainers to db_owner again and then I can edit. I can log on with my admin account and edit also. I'm beggining to see why the last administrator left the Trainer group in the db_owner role. I'm thinking of populating the form with an ado recordset based on the same sproc but populate it in the On Load event.
0
 
AkAlanAuthor Commented:
I'm closing this post and awarding points since I was able to edit records on other Access forms that are unbound and I believe my new issue to be more with Access and how stored procedures can work with forms. The question I asked is SQL realated and has been correctly answered. Thanks for the help jmiller.
0
 
AkAlanAuthor Commented:
I'm posting a new question realted to Access forms, thanks for the help.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now