NAVISION over SQL (An ODBC security Problem?)

Hi! This year my enterprise has began to use NAVISION Attain 3.70 over Sql Server (Microsoft Sql Server 2000 Sp3). We use a windows login authentification (NAVISION feature). When we create a new user NAVISION create a SQL User on the server inside public role from Microsoft SQL Server 2000. If I manage roles in NAVISION and I deny access to Customers table at specific user (we named USER-A) then this USER-A can not access at customer profiles through NAVISION. if we create an ODBC using USER-A login (user and password) then that user will be able to access all NAVISION data. Even Customer Table. So, does is my NAVISION & SQL misconfigured? if this is the case how must I configure NAVISION + SQL to solve this problem? Maybe it’s a security problem. I thought that NAVISION use Application Role to manage security but now I am not sure. Thanks – ROger Manich

These permissions must have been granted outside of Navision (e.g. making the user a member of the db_owner role), because Navision does not do it. Firstly, users created within a SQL database cannot be members of (or not members of) ‘public’. Public is a built in role to represent all users, similar to the Everyone SID in windows. It is permissions to objects that can be granted to public - not user accounts. Database Logins (database authentication): When you create a database login ‘garfield’ in Navision (Tools/Security/Database Logins) there must already be a ‘garfield’ LOGIN on the server. Assuming there is, Navision creates a ‘garfield’ USER in the database, with no permissions to any objects in that database. Windows Logins (windows authentication): When you create a windows login ‘corporate\garfield’ in Navision (Tools/Security/Windows Logins) Navision will create a ‘corporate\garfield’ LOGIN on the server if it is a valid windows account and a ‘corporate\garfield’ USER in the database, again with no permissions to any objects in that database. In both cases, there are no raw permissions in SQL Server for that login/user unless they have been explicitly granted there. Be careful with windows logins - if you enter an account that is an administrator of the server machine, even if via a windows group, he will then be a sysadmin of the SQL Server and a database owner (member of the db_owner role) of all databases. A db_owner member has all permissions to all objects in a database. You are correct about the application role - Navision uses app roles to gain additional permissions to objects for users that ordinarily have no permissions. So check what permissions your user has in the windows.

Thanks for your atention, Robert. I check your information and is almost correct. It means that NAvision create a SQL Server user and put this user insider public role of NAVISION Databse. IN fact there is no problem because this one disable any NAVISION user to use ODBC (What I want) and now I can grant access through ODBC at I want to. Thanks for your help. It has been apreciated. – Roger