Microsoft Access using SQL Server linked tables

I have a Microsoft Access database using linked tables to SQL Server.

How can I ensure that the database user cannot open the tables? For example, if I have 2 users, each with their own subset of data, how can I prevent them from opening Access, and just opening the tables from the left-margin. I have experimented with Hidden Objects, etc, but this is not entirely fool-proof.

Do I need to do something with SQL Server?
Rick DangerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

ste5anSenior DeveloperCommented:
You cannot prevent it.

From the Access viewpoint, it makes also no sense. Cause you gave the users the permission on those tables, thus he can open it.

So what is your current problem you're trying to solve?
0
Rick DangerAuthor Commented:
I am trying to prevent the users being able to look at the tables.
0
ste5anSenior DeveloperCommented:
Well, think about it. The user must see the data. The form doesn't matter.

p.s. especially as the user can also connect for example via Excel and see the tabular data.
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

Rick DangerAuthor Commented:
I have thought about it - hence the question!

But with a form, I can govern how, and to whom the data is displayed.

Is there not a way whereby I can utilise the Users facility within Security in SQL Server? Or some other way?
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
At the SQL server level you have given users permission to access the table. Thus, they can, and they can see all within it. Note that this also applies for those of your users who are smart enough to open a connection to SQL server from Excel, or SSMS.

So the correct way to do this, is to create a view, on the server that filters the rows that should be visible. Then create a linked table against that view (instead of the table). From access it will appear just as if it was a "standard" linked table, but the filtering is now done server side, so this means we have now also covered the scenario where people use excel or SSMS to connect.
1
Rick DangerAuthor Commented:
Anders
That's a great suggestion!
0
Dale FyeOwner, Developing Solutions LLCCommented:
You can also hide the navigation pane in the Access options, and if you uncheck the use Special keys checkbox (also in Access options) , then only the most experienced Access developers will know how to bypass the blocking of special keys.
0
Rick DangerAuthor Commented:
Anders
Would that mean I'd need a different View for each user of the relevant tables? Not too sure how I would create a filtered View, based on a user.
0
ste5anSenior DeveloperCommented:
So what is your current problem you're trying to solve?

The question still is: What is your problem?

E.g. not every user should see every row? Then this is solved by row level security as already posted. A possible server-side implementation using Windows integrated authorization:

USE tempdb;
GO

CREATE TABLE Payload
    (
        ID INT NOT NULL PRIMARY KEY ,
        Payload NVARCHAR(255) NOT NULL
    );

CREATE TABLE Groups
    (
        GroupID INT IDENTITY NOT NULL PRIMARY KEY ,
        GroupName NVARCHAR(255) NOT NULL
            UNIQUE
    );

CREATE TABLE Users
    (
        SamAccountName sysname NOT NULL PRIMARY KEY ,
        UserName NVARCHAR(255) NOT NULL
            UNIQUE ,
        GroupID INT NOT NULL
            FOREIGN KEY REFERENCES dbo.Groups ( GroupID )
    );


CREATE TABLE PayloadPermissions
    (
        PayloadID INT NOT NULL
            FOREIGN KEY REFERENCES dbo.Payload ( ID ) ,
        GroupID INT NOT NULL
            FOREIGN KEY REFERENCES dbo.Groups ( GroupID ) ,
        PRIMARY KEY
        (
            PayloadID ,
            GroupID )
    );
GO

CREATE VIEW FilteredPayload
AS
    SELECT P.*
    FROM   dbo.Payload P
           INNER JOIN dbo.PayloadPermissions PP ON PP.PayloadID = P.ID
           INNER JOIN dbo.Users U ON U.GroupID = PP.GroupID
    WHERE  U.SamAccountName = SUSER_SNAME();
GO

Open in new window

0
Rick DangerAuthor Commented:
I don't have a specific problem, other than trying to figure out a way forward with a concept.

Your solution is good, but the question remains, and that is how do I let the View know which User is trying to access the data, I understand what you have shown me here, and I understood the solution from Anders. So if I have User A, accessing their data through Views using linked tables via Access (so they are in Access), how would I make the View show only User A's data. How do I parse the User ID from Access to the View?
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
If users are connecting through windows AD, you can get their username by the function Stefan showed in his example SUSER_SNAME();

So its the same view for ALL users, but it is filtered differently, depending on their windows AD.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ste5anSenior DeveloperCommented:
Your original question is answered: You cannot prevent users from accessing linked tables in Access. That's how Access UI is designed.

I don't have a specific problem, other than trying to figure out a way forward with a concept.
You need to phrase your problem first, before any solution makes sense.

So if I have User A, accessing their data through Views using linked tables via Access (so they are in Access), how would I make the View show only User A's data. How do I parse the User ID from Access to the View?
So is your problem row level security or data isolation?

You need SQL Server side logic for row level security, Thus any user id on the client (Access) is not safe. Keyword: SUSER_SNAME(). The easiest implementation is using Windows integrated security.

Just test my sample using different Windows user..
0
Rick DangerAuthor Commented:
Thanks for your help, much appreciated
0
PatHartmanCommented:
I see that you have accepted an answer but I will share what I do anyway.  In situations where my users are technically savvy, I have the DBA create specific logons for them.  I don't use Windows authentication.  I also don't give the users their password.  The user logs in to the Access app using the assigned UserID and his "Access" password.  The Access app then links to the SQL Server table using the assigned UserID and a CALCULATED password.  That way, if you hide the code from the user, he won't be able to see how his password is derived and so he won't be able to take it upon himself to just create an empty database and link directly.  I'm not sharing the method the DBA and I decided on to calculate the passwords but you should be able to come up with something using information from the user's account information.

You still have to to the typical things such as:
Hide all tables and queries
Hide the navigation pane
Prevent Shift Bypass
Compile to .accde
Rename the .accde to .accdr

Another advantage of the above method is that they also cannot get to the database via Excel.
0
Rick DangerAuthor Commented:
Pat
Thanks very much - your wisdom has always been greatly appreciated in many of my past questions.
0
PatHartmanCommented:
You're very welcome.
0
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
Microsoft Access

From novice to tech pro — start learning today.