I want to say thanks to all the folks that responded to my last post. I’m needing your help again. We have a table in Navision that is “linked” to a view in SQL. This view accesses data in another database on the same server. Is there a way I can have my users be able to access this table without giving them DBO rights on the Navision DB? I know in the manual it says you have to, but on our development server I’m able to set up users with just public and datareader access on BOTH DB’s and it works just fine. I thought it was a configuration issue, but after working with my DBA for a few days, we are stumped. My DBA is concenred with giving the users DBO access because some of our users also use Enterprise manager/Query Analyzer to directly access data and he doesn’t want them to mess anything up accidentally. Thanks for all your input and help/comments/suggestions. Brian
Hi, The reason for this requirement is that for non-db_owner role SQL server users, Attain activates a SQL Server “application role” immediately after logging in, which is used to boost permissions to the full set on all tables in the database. Then, the Attain permission systems sits on top of this to control permission to individual tables. Unfortunetly, in SQL Server the activation of an application role does not give any permissions in any other databases on the server except for the permissions of the guest user, if present (which is useless). Which means there would be no access to your view from Attain. So, only db_owner users can access Attain linked objects since in this case the application role is not used. Sorry. As I remember, the manual suggests treating another database on the same server as a SQL Server Linked Server (which is normally a separate server but can be the same). You have to set up the security of a linked server manually (e.g. in Enterprise Manager) to give each user access to the linked server. But it should work ok even though the application role is still being used by Attain (Attain does not know that it is a linked server - it just sees the view). The view is then defined on the linked server object using four-part naming (server.db.owner.object) instead of just the three-part. Try it.