Granting two different types of permission to SQL tables

We have a financial application with a SQL backend.  There is a users group for the application users that allows them to write/edit the data in the tables.

Now, ownership has come to me and wants me to allow a member of that application user group the ability to access the tables via ODBC and MS Access, but have read-only permissions on the tables.  Every thing I've tried so far does not work.  He still can edit the data in MS Access.

Any suggestions?

Bill LalorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike WolfePresident and Lead Software DeveloperCommented:
Are you using Windows Authentication or SQL Server Authentication when connecting to the back end?
Dale FyeOwner, Dev-Soln LLCCommented:
Not an expert in this area, but I suspect that if the user is assigned to an AD group which has read-write permissions to the data in this particular table on SQL Server, then you probably will not be able to override that in Access if the tables linked in the application use Windows Authentication.

However, you should be able to create a new Database role (not sure that is the correct term) which only has read-only access to that table.

Then, give him an Access database which has that table linked via SQL Server authentication with that role and the password you assigned it.

DBAduck - Ben MillerPrincipal ConsultantCommented:
Yes, Active Directory permissions are cumulative so if they have readonly in one group and read/write in another, they will have read/write permissions.

They would have to either use SQL Authentication or login/launch the application as another user.
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Mike WolfePresident and Lead Software DeveloperCommented:
If you explicitly DENY permissions to a user, it will override GRANTed permissions on the same object.

For example, I'll assume you have a SQL Server database user named Accounting, which is tied to a SQL Server server login to which all employees of the Accounting department belong (this server login may use Windows or SQL Server authentication).  Let's say we've granted all the users of the Accounting department full access to modify all the tables by making the Accounting login a member of the db_datareader and db_datawriter database roles.

You can create another SQL Server server login for the one employee you want to restrict.  We'll call him anewguy (this could also be an Active Directory group, such as Interns).  You would create a corresponding database user for the server login and explicitly DENY him modify permissions to the relevant tables, like so:


Open in new window

This user would have all the access of the group to which he belongs, less any access that you specifically DENY.  Note, you cannot simply REVOKE his access to those tables, because he would still maintain access by virtue of his membership in the group.  This is the main difference between DENY and REVOKE in SQL Server.
DBAduck - Ben MillerPrincipal ConsultantCommented:
Good write up, but this still won't work for the same user getting different permissions. If the Windows User has read/write because he is in a group for an application, that same user will not be able to have more restrictive permissions.  If you DENY or REVOKE privileges to the user, then that user would have less permissions in the application.

If have misunderstood your write up, I apologize.
Dale FyeOwner, Dev-Soln LLCCommented:
Since this is about using Access to view the data in that table, I still think the best bet is to create a new role in SQL server with SQL Server authentication.  This role would need read only access to the specific table in question.

In Access, you would link to that table using SQL Server authentication and the UserID/Password associated with that role.  Then, that user would still have Read/Write in the financial application but only Read permissions when viewing the data in Access.

Mike WolfePresident and Lead Software DeveloperCommented:
Ben Miller, after re-reading the question more carefully, I see my mistake.  I assumed that everyone was using a single application in MS Access and that ownership wanted a single user to have more restricted access than the others in that group.  My suggestion was for that situation.

After closer reading, it appears that the goal is for the same person to have read-write access through the "financial application" but read-only access to the same tables via a separate MS Access front-end.  Assuming that's a more accurate description of the situation, then Dale Fye's suggestion seems to be the more appropriate one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.