SQL Views and Permissions issue

tranceport

Hi!

Basically we have created a Table in Navision which is linked to a SQL View. The SQL/Nav User has permissions to access the table data of this view. The user is set only as guest and we cannot set the user as db_owner.

When we run the table inside Navision no data is loaded into it. However if we log on to SQL Management Studio with this user and Query the view we get the results. We ran the SQL Query profiler and ran the SQL statements inside SQL and the results came through.

There seems to be something we are missing (permission or specific role?) in between to allow SQL to return the view results to Navision.

Any Ideas?

Thanks

modric

First - which version of SQL? Security management differs a lot in SQL2000 and 2005...

tranceport wrote:

The user is set only as guest and we cannot set the user as db_owner.

You can set the user as db_datareader - at least I had to do so for users who are allowed to run Stored Procedures for reporting purposes on SQL2000. However, in SQL2005 it should be enough to give a particular user EXEC rights to SP - he can't read underlying Tables directly, but SP can.

Seemingly the same applies to Views - just examine what permissions are granted to user for View in question, and WHO has granted these permisions (2005) - maybe the grantor himself has no rights to do so... Grantor must be dbo or db_securityadmin or have "With Grant" permission himself.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]

stryk

"Normal" NAV users just have the role "public"; so maybe you have to grant rights for your view to that role:

GRANT SELECT ON "MyView" TO PUBLIC

David_Singleton

stryk wrote:

"Normal" NAV users just have the role "public"; so maybe you have to grant rights for your view to that role:

GRANT SELECT ON "MyView" TO PUBLIC

=D> Great idea. I have always been manually creating a separate role and giving that access to the view/table, then assigning the users to the new role. This is much simpler.