Access from NAV to a SQL view in different DB not allowed

Answers

• Options

  Lars_Westman Member Posts: 116

  2011-11-25

  Unfortunately You either have to enable the guest account and give that account access to You NAV db or setup the users that need to access the linke table as db_owner in the NAV db (along withpermissions in the database the view reads. I would prefer the latter since You still set the permissions inside NAV that restricts what the user can do inside NAV. However if the users gets hold of SQL Management Studio or any other tool and connects to the db they can do stuff You wouldn't like. But at least in this scenario You know who the user is. Only You can decide what suits your situation.
  
  http://msdn.microsoft.com/en-us/library/dd355092.aspx

  Lars Westman
  http://www.linkedin.com/in/larswestman

  0
• Options

  bastir Member Posts: 27

  2011-11-25

  Hi Lars,
  
  thanks for your reply.
  Unfortunatly our users can use tools like SQL Mgt. Studio or even hav Excel installed (it is really to easy to use excel to access the data if you are db_owner)
  
  But I also cannot move the tables from the other database to the NAV database.
  
  Could you please explain me a little bit more the solution of the guest account.
  Where do I've to enable the account.
  
  Is it not possible to access the remote database from NAV with a user, that has db_owner rights in the target database.
  Which user/role is logging in to the other database ndo$shadow or another user/role?
  
  Thanks in advance
  Sebastian

  0
• Options

  ndbcs Member Posts: 31

  2011-11-27

  As long as the user is not db_owner NAV uses a application role ($ndo$shadow in standard security model) to access the NAV db.
  The application role is only known in the NAV db. Imho there are no chances to access an other db with an application role.
  
  In that situation I always try to implement a solution where only a NAS needs to access the other DB and give the NAS account db_owner role.
  
  Another alternative is to use a linked server. You can store credentials in the linked server configuration.
  Your view uses the linked server to access the other db. So the view uses the credentials of the linked server.
  I really don't like that solution, but it works ...
  
  The last alternative I'm aware of, is to implement some kind of replication.
  
  It depends on your needs, what the best solution is.
  
  Tobias

  0
• Options

  bastir Member Posts: 27

  2011-11-27

  Hi Tobias,
  
  thanks for the detailed answer, I also believed that the ndo$shadow role is preventing us from accessing the "remote" tables.
  So I'll test a little bit more, strange that is is better to put the database to another sever and create a linked server connection, than setting credentials to the view.
  
  I'm missing some statement like
  

  CREATE VIEW foobar ... EXECUTE AS [UserWithDBOwnerRights]
  

  the same you can use with stored procedures.
  
  Best regards and "einen ruhigen ersten Advent"
  
  Sebastian

  0
• Options

  ndbcs Member Posts: 31

  2011-11-27

  You can also create a "loopback" linked server (link the server with itself).
  
  EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
  EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LOCALHOST',@useself=N'False',@locallogin=NULL,@rmtuser=N'userWithPermission',@rmtpassword='########'
  
  Really nasty...
  
  Dir auch einen ruhigen ersten Advent.
  Tobias

  0
• Options

  Lars_Westman Member Posts: 116

  2011-11-28

  But the main issue remains: You still need to be db_owner in the NAV database to access data outside the NAV db.

  Lars Westman
  http://www.linkedin.com/in/larswestman

  0
• Options

  ndbcs Member Posts: 31

  2011-11-28

  No, not with the linked server approach.
  
  Tobias

  0
• Options

  bbrown Member Posts: 3,268

  2011-11-28

  ndbcs wrote:

  No, not with the linked server approach.
  
  Tobias

  
  But the database is on the same server. So "Linked Server" doesn't apply. Use the Guest account.

  There are no bugs - only undocumented features.

  0