[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL PErmiisions using MS Access as front end

Posted on 2011-03-12
4
Medium Priority
?
401 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 2000 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

607 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