SQL Server error ... accessing the Sessions table

rnshhrnshh Member Posts: 22
edited 2008-01-28 in SQL General
We've been struggling with variations of the same theme of error message. Generally, the message is:

The following SQL Server error(s) occurred while accessing the Sessions table:

229,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'Session', database '<database name>',schema 'dbo'.

We're on Version NA 4.00 SP1 (4.0 SP3)
SQL Server 2005
Citrix ICA

We've been synching, but nothing works. The only solution is to set the users role to Super (Data) or Super.

Thanks for any pointers.
Tim Lecander


  • kinekine Member Posts: 12,562
    I had this problem too. I solved it by adding select permissions for public on this view in NAV database. It seems that if you are using the Session table in Codeunit 1 for some purpose, you have no rights. After the CU1 is processed and the company is opened, all is ok.
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • krikikriki Member, Moderator Posts: 9,079
    [Topic moved from Navision forum to SQL General forum]
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!

  • strykstryk Member Posts: 645
    You could try to fix this by executing this little TSQL within Management Studio:
    use [MyNAVdb]
    grant select on "Session" to public

    Does this help?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • rnshhrnshh Member Posts: 22
    Sorry it took so long to post a reply.

    We ended up adding db_owner for each user's database role membership.
    Tim Lecander
  • ara3nara3n Member Posts: 9,253
    That's not a correct solution. User should not have dbOwner rights.
    Ahmed Rashed Amini
    Independent Consultant/Developer

    blog: https://dynamicsuser.net/nav/b/ara3n
  • garakgarak Member Posts: 3,263
    Wow wow :!: Do not give normal users the permissions to DB_Owner :!:
    Thats fatal :!:

    With using 4.03 and standard security model and you read in your source the session table (view), u must grant permissions to this view. All user use database role public. so give under Database -> Security -> Roles -> Database Roles -> Public -> Securables -> Add -> Specific Objects -> Views -> [dbo].Session -> Permission Select -> Grant. These are steps for SQL 2005.

    Easier go to Database -> new querywindow -> grant select on "Session" to public

    Do you make it right, it works too!
Sign In or Register to comment.