Solved

SQL PErmiisions using MS Access as front end

Posted on 2011-03-12
4
360 Views
Last Modified: 2013-11-28
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
Comment
Question by:AkAlan
  • 3
4 Comments
 
LVL 18

Accepted Solution

by:
Jerry Miller earned 500 total points
ID: 35118093
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
 
LVL 6

Author Comment

by:AkAlan
ID: 35118395
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
 
LVL 6

Author Comment

by:AkAlan
ID: 35118534
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
 
LVL 6

Author Closing Comment

by:AkAlan
ID: 35118540
I'm posting a new question realted to Access forms, thanks for the help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question