rachelgeoghegan
Programmer
I am having a problem which has been discussed here already although the answers given do not solve the issue.
The problem relates to preventing external access to sql server 2000 data from other external applications, for eg. MS Excel, MS Access. When you take the 'Get External Data'/new database query option in excel you are allowed to gain access to data in tables that you do not have permisssion to view.
For example I have created a user defined database role which only allows access to a no. of crucial stored proceedures & read only access to 1 table in the database - the idea being that anyone who uses the software application which accesses the database must be a member of that role otherwise they can't even log in.
This works perfectly when users are trying to access the data from Enterprise Manager or the software application (VB6).
This problem also applies to the import facility in MS Access - you can import data from tables in the database that the user doesn't even have read-only access to. I'm sure it applies to any external applications which use ODBC...
I have even denied access to these tables (by adding the user to the denydatareader role as well as the user defined login role as mentioned above) and still the same.
There has to be a workaround to this as it is a huge breach of security, I think I'll just go and import the SQL payroll table into ms excel!!! Can you imagine?
Any comments/ ideas would be most appreciated.
A frustrated Irish developer!!!....
The problem relates to preventing external access to sql server 2000 data from other external applications, for eg. MS Excel, MS Access. When you take the 'Get External Data'/new database query option in excel you are allowed to gain access to data in tables that you do not have permisssion to view.
For example I have created a user defined database role which only allows access to a no. of crucial stored proceedures & read only access to 1 table in the database - the idea being that anyone who uses the software application which accesses the database must be a member of that role otherwise they can't even log in.
This works perfectly when users are trying to access the data from Enterprise Manager or the software application (VB6).
This problem also applies to the import facility in MS Access - you can import data from tables in the database that the user doesn't even have read-only access to. I'm sure it applies to any external applications which use ODBC...
I have even denied access to these tables (by adding the user to the denydatareader role as well as the user defined login role as mentioned above) and still the same.
There has to be a workaround to this as it is a huge breach of security, I think I'll just go and import the SQL payroll table into ms excel!!! Can you imagine?
Any comments/ ideas would be most appreciated.
A frustrated Irish developer!!!....