Solved

SQL PErmiisions using MS Access as front end

Posted on 2011-03-12
4
309 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now