LinkedObject External Database Security

doddwell

Hello

NAV 4.1, SQL Server 2005

I have created a SQL Server view that reads data from a different database. I then created a NAV table and connected it to the view using the "LinkedObject" property (I also set the "LinkedInTransaction" Property to "No").

Running the NAV table will only work if the user running the NAV table has the dbo_owner role. I can't give my users this role - is there a way around this:

Hardcode the sa password somewhere into a codeunit/form?
Do something with Application Roles?

Many Thanks

kine

Yes, the problem is the applicatio role. To be able to have this cross-database ownership or how it is named, you need to enable it (look for "Cross-Database Ownership Chaining"). And, if I remember correctly, you need to enable Guest user in the target database, but you do not need to assign any permissions to this user. It just must be enabled.

bbrown

Create a linked server from your NAV server to the other server. Set a remote login for its security context. It's this account that needs rights to the remote data.

kine

From the post I understand, that the second database is on same server, not remote one...

doddwell

Hello Kine - the database is on a remote server.

Hello bbrown. I took the steps that you suggested. Unfortunately, the user running NAV still needs the dbo role in the NAV database in order to be able to run the NAV table. This is what I did:

1 - Created a new SQL server user with the dbo role in the remote database.
2 - Connected to the remote SQL Server using the security context in used in step 1.
3 - Enabled the guest account on the remote database

Any ideas?. Thanks very much.

David_Singleton

I normally work things out to put both databases on the same server instance, but now I just hit an issue with a NAV 3.7 to NAV 2009 interface. Problem being that 3.70 will not run on anything after SQL 2000 and 2009 Wont run on anything before 2005. So separate servers is the only option.

All doable of course, but never as simple as it first seems.

doddwell

David. If I can get the databses on the same server, how would you do it? Nav 4.1 and SQL Server 2005.

Thanks.

David_Singleton

If its on the same server then you just need to give the user access in SQL to that particular table. Also often I find that when there is an application where a Navision user needs access to a table in a different SQL database, then what ever application that database is owned by gives that user permission anyway.

Actually the issue I have is more that the 3.70 server, has Database users, and for historical reasons I can't create them as Windows users. But in the end the problem is similar so the solution will be also. And to be honest, the 2009 DB is still a test db, so the users are DBO there till I fix it.

doddwell

Hello David

I have tried that. I gave myself the dbo Role in the sister database but I still cant run the table in NAV that connects to the table in the sister database. All is well if I give myself the dbo role in the NAV database though.

Any ideas? Thanks