mibuso.com
Home› NAV/Navision Classic Client
Options

SQL User in Linked Table

DenSterDenSter Member Posts: 8,304
edited 2005-10-11 in NAV/Navision Classic Client
I have 2 databases on my SQL Server, one of which is a Navision database. For this integration piece that I wrote I created views into the other one in the Navision database, and created linked tables to get to the data. Everything works fine, the users accepted the solution, so today we were ready to take it live.

Everything installed, views created, the sys admin at the customer was able to open the linked tables, so it all connects fine. Then, we added the first user to the system (to both databases, just like in testing), and the first time try to access any of the data, they get a SQL error 'user X is not a valid user on the database'. I verified this with my own eyes, and the user exists in both databases. The only way that we could make this work is to make the user system administrator on the SQL Server.

Does anybody here know how I can give regular database users access through these views without giving them system admin rights?
Daniel Rimmelzwaan
RIS Plus, LLC
MVP - Business Apps

Answers

  • Options
    ovicashovicash Member Posts: 141
    Did you gave acces on the table/view to the user ?
    I mean in Sql Server Enterprise manager-> Views-> All Tasks-> Manage Permissions?
    :?:
    ovidiu

    Best Regards
  • Options
    DenSterDenSter Member Posts: 8,304
    I can't find views -> all tasks -> manage permissions. Please be more specific.

    I don't think it is about permissions, for some reason it is not recognizing the user, so it is not even letting the credentials into the database. You first have to get into the database, and then after you are in, it worries about permissions to specific objects in the database. I need to know how to make SQL Server recognize the user without having to give it server admin rights.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    DenSterDenSter Member Posts: 8,304
    It can't be permissions. I set the user to db_owner, so that gives them all permissions. When I try to open the linked table in Navision it still gives me this message :
    916,"08004",[Microsoft][ODBC SQL Server Driver][SQL Server]Server user 'johnny' is not a valid user in database 'MyDatabase'
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    Marije_BrummelMarije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Did you had a look at

    http://www.mibuso.com/forum/viewtopic.php?t=7706

    Maybe it is the same problem?
    Marije Brummel | Freelance Business Central Specialist
  • Options
    DenSterDenSter Member Posts: 8,304
    Thank you Mark, I had found the same article, but it was no help. I tried all those suggestions and I still get the error.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    DenSterDenSter Member Posts: 8,304
    Okay, I found an answer. Read all about it here. The answer was to simply grant db_owner to all database users in the Navision database, and take away system administrator. I'm still not completely satisfied though, because not all users should have to be db_owner to get access to data.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    ovicashovicash Member Posts: 141
    DenSter wrote:
    I can't find views -> all tasks -> manage permissions. Please be more specific.

    I don't think it is about permissions, for some reason it is not recognizing the user, so it is not even letting the credentials into the database. You first have to get into the database, and then after you are in, it worries about permissions to specific objects in the database. I need to know how to make SQL Server recognize the user without having to give it server admin rights.

    I meant: after you open a database in Enterprise Manager there are (diagrams, tables, views....). If you select a specific view you can find in properties -> all tasks-> manage permissions.

    I don't know if it helps but I know that you must give permissions to a view if the user who wants to connect isn't a sys admin or db_owner
    ovidiu

    Best Regards
  • Options
    DenSterDenSter Member Posts: 8,304
    Ah now I see what you mean, thanks for explaining :). I tried the permissions on the view, and I even explicitly set the same permissions on the table itself, but as soon as I take away db_owner it says that the user is not a valid user in the target database.
    Daniel Rimmelzwaan
    RIS Plus, LLC
    MVP - Business Apps
  • Options
    ovicashovicash Member Posts: 141
    DenSter wrote:
    Ah now I see what you mean, thanks for explaining :). I tried the permissions on the view, and I even explicitly set the same permissions on the table itself, but as soon as I take away db_owner it says that the user is not a valid user in the target database.

    And now a stupid question. I've seen it at a client :mrgreen:

    Does the database the option from Database->Atler->Options->Acces

    "Memebers of db_owner, dbcreator or sysadmin"

    Activated ? :whistle:
    ovidiu

    Best Regards
  • Options
    kinekine Member Posts: 12,562
    May be that there is problem with the Shadow role (application role)... Navision create app role in the database which will be connecting... For each db is new app role created and granted permission for the DB.

    Now, you have 2 DB and 2 App roles. You connect view in one DB to table in another DB. You start Navision, it connect with the app role to the 1st DB and try to read data from 2nd DB - no permission. Why? Because the app role have no rights to read data from 2nd DB...

    May be that problem is there...

    If the user is db_owner, he will be connected as db_owner and not as application role used by Navision... (it has higher priority)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
Sign In or Register to comment.