Granting two different types of permission to SQL tables

Bill Lalor
Bill Lalor used Ask the Experts™
on
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?

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike WolfePresident and Lead Software Developer

Commented:
Are you using Windows Authentication or SQL Server Authentication when connecting to the back end?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Dale

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mike WolfePresident and Lead Software Developer

Commented:
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:

DENY UPDATE, INSERT, DELETE ON Invoices TO anewguy;
DENY UPDATE, INSERT, DELETE ON Products TO anewguy;

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.

Commented:
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 LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Dale
Mike WolfePresident and Lead Software Developer

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial