Securing tables in SQL Server accessed via ODBC

Legacy Contributor

Moving from Unix/Oracle to Windows/Sql Server. I'm trying to figure out a way to allow users to only access specific tables via ODBC. For instance, only allow an AP user to link to APINVOICE, APVENMAST and GLNAMES and restrict them from seeing data in any other table. I thought I could do this by creating a schema APX with select only rights to those tables, but the user can still see all of the other Lawson tables (and select on the data). I tried db_denydatareader, but then I couldn't see any tables. SS appears to give implicit access to all tables (like LAUA) while with ORACLE I had to explicitly grant access to a table (like LS). tia

Legacy Contributor

Here is an example of something I used for a Read Only ODBC connection. I'm not sure if the user will be able to see the other tables but they shouldn't be able to see the data within.

-- Create Role

USE [prod]
GO
CREATE ROLE ODBC_APVENMAST_ONLY AUTHORIZATION dbo;
GRANT SELECT ON [dbo].[APVENMAST] TO [ODBC_APVENMAST_ONLY] GO

-- Add ODBC Users

USE [master]
GO
CREATE LOGIN [DOMAINjdoe] FROM WINDOWS WITH DEFAULT_DATABASE=[prod]
GO
USE [prod]
GO
CREATE USER [DOMAINjdoe] FOR LOGIN [DMAINjdoe]
GO
USE [prod]
GO
ALTER ROLE [ODBC_APVENMAST_ONLY] ADD MEMBER [DOMAINjdoe]
GO

VID021_Grid_Column Default Sorting.zip