Hi All

Wonder if you can help, I work for a company that develops an accounting
package that uses SQL server as the RDBMS.

One of the many features that we have pushed regarding our product is that
accountants and users alike can use a program such as MS Excel to generate
basically any report they want.

However, since visiting a client they have brought to my attention a major
problem. In our program, we limit the menus that certain users can get to
by checking what SQL role they are in. For example, if Joe Bloggs logs in
and he is a member of the Sales role then because his supervisor has set
AstrA Menu Permissions (our own window) to say that Sales can't get into
Purchasing then Joe Bloggs finds that he can't get in purchasing.

The problem is that since I demonstrated to our client that there sales
staff can create their own reports, our client noticed that when I logged in
with Joe Bloggs username and password via Excel's Get External Data window,
I had full access to all the tables in the SQL database.

So my question is, is there anyway that we can limit what tables are shown
in the Add Tables section of Ms Excel/Query so that certain users can only
get to certain tables??

Thanks

Laphan

I don't think there's anyway to limit the tables available (visible), but it's
certainly possibly to prevent users from selecting from them.

You say: <<by checking what SQL role they are in.

You'll need to go back to that role and set the SELECT permissions on only the
tables that the SALES people need.

Sure, they might be able to "see" every table in MS Query, but they should only
be able to select records from tables with the appropriate SELECT permissions.
If the add a table w/o SELECT permission, the driver should return an error
like "user does not have the appropriate permsission on object <table"

The bottom line is that you can't limit the tables "showing up" in MS Query;
you'll have to control this from SQL Server.

Thanks for this DCM

I think we may need to go the web intranet route (ie create an IE form of
the data they are allowed) because our accounts system doesn't have cut and
dried tables so even though a sales person isn't allowed to look at
purchasing table, the nominal side of both the sales and purchase tables use
common data and this may limit the data in some way.

Your comments have proved food for thought though and I'll pass it onto our
gurus for them to suss out.

Thanks

Laphan

DCM Fan wrote in message
...
I don't think there's anyway to limit the tables available (visible), but
it's
certainly possibly to prevent users from selecting from them.

You say: <<by checking what SQL role they are in.

You'll need to go back to that role and set the SELECT permissions on only
the
tables that the SALES people need.

Sure, they might be able to "see" every table in MS Query, but they should
only
be able to select records from tables with the appropriate SELECT
permissions.
If the add a table w/o SELECT permission, the driver should return an error
like "user does not have the appropriate permsission on object <table"

The bottom line is that you can't limit the tables "showing up" in MS Query;
you'll have to control this from SQL Server.