CODEWITHSUNDEEP
sql-serversql-server-2008ms-access
Slee
Slee

Reputation: 28248

allow Access Database to talk to SQL Server database with restrictions

I have a customer who wants to run all their reporting in Access but they need data from my SQL Server database and I need to be able to filter what data they see by a few parameters. The data needs to be updated frequently, what is the best way to handle this.

Upvotes: 2

Views: 172

Answers (2)

RolandTumble
RolandTumble

Reputation: 4703

My suggestion would be to create Views that do any permanent filtering (that is, that filter what you allow the Access user to see). Then create a login with Read on those Views.

From the Access end, link to the Views with Get External Data (by whichever method applies to the Access version), for an ODBC Connection, which contains the login credentials if you're not using Integrated Security on the SQL Server. The Views show up as tables & the user can report to their heart's content.

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

You could create a login with read permissions on your prod db, or a login with read permission on "publication db" where you would copy the visible info nightly.
Your question needs to be more precise in terms of timing (real time info or not) and granularity (all details or summarized data), at least.

Upvotes: 2

Related Questions