Hi!
Well, it doesn’t matter which NAV “Security Model” is set. All “normal” users are assigned to the server- and database-role “public”. Thus, if they are able to logon to the server/database e.g. with non-NAV tools, they have all access-rights of this role.
If you want to restrict access I could think about two options:
- Modify the database role “public” by DENYING the access-right for those tables you want to restrict. For example:
USE [Navision]
GO
DENY SELECT, INSERT, UPDATE, DELETE ON [dbo].[CRONUS$Item] TO [public]
GO
This would mean, if the user is accessing the db via NAV the “application role” ("$ndo$shadow" with “Standard”, “$ndo$ar$…” with “Enhanced” Security Model); else the - modified - “database role” “public” would be used.
But actually it is not really recommended to fiddle with standard roles. So I would go for this option:
- Create a new “database role” where you set up all table restrictions, e.g. “nav_restrictions”:
USE [Navision]
GO
CREATE ROLE [nav_restrictions] AUTHORIZATION [dbo]
GO
DENY SELECT, INSERT, UPDATE, DELETE ON [dbo].[CRONUS$Item] TO [nav_restrictions]
GO
Assign this role to the relevant users, thus these users have the roles “public” and “nav_restrictions”!
USE [Navision]
GO
EXEC sp_addrolemember N’nav_restrictions’, N’UserXYZ’
GO
Have in mind that a DENY right will always overrule a REVOKE!
If you want to deny access to all tables from this database you don’t need to create your own db role, then you could simply assign the db role “db_denydatareader”:
USE [Navision]
GO
EXEC sp_addrolemember N’db_denydatareader’, N’UserXYZ’
GO
Again, these roles are only effective if the user is accessing without NAV - with NAV always an “application role” will be used which overrules the “database roles”.
Hope this suits your requirements.
Regards,
Jörg