I'm running NAV 2009 R2 (Build 32012) classic client under SQL-2008 R2, simple security model, no traceflag.
I can't manage do display all sessions for a non-sysadmin sql-database login with traceflag 4616 set off (with traceflag set on, it works).
My database login TEST has got "VIEW SERVER STATE" permissions. (Each sql server login gets automatically VIEW SERVER STATE permission when the login is synchronized by the NAV client - in the simple security model this happens, when the database login is created within NAV). Furthermore the server public role has got "VIEW ANY DATABASE" permission by default (http://msdn.microsoft.com/en-us/library/ms189077.aspx
). So the effective server permissions of TEST are "CONNECT", "VIEW SERVER STATE", "VIEW ANY DATABASE".
I recognized, that NAV client launches the following query:
SELECT COUNT(*), ... FROM [master].[dbo].[sysprocesses] within a SQL batch, in order to get and display the number of sessions. (sys.sysprocesses is a system-view within the master table)
When I do this query from SSMS, depending on the VIEW SERVER STATE permission granted or not to the user TEST and the EXECUTE AS LOGIN='TEST' activated, I get the expected result (1 session or more, respectively).
So it must have to do something with the application role $ndo$shadow under which the query is launched from NAV client.
Indeed, according to http://msdn.microsoft.com/en-us/library/ms190785.aspx
, the only way to let an application role see server-level metadata (like sysprocesses) is either to activate traceflag 4616 or to use a certificate-signed stored procedure.
This seems to have been a fact already in SQL Server 2005. See http://support.microsoft.com/kb/906549/EN-US
, e. g.
From that point of view the failure seems quite logical...
But according to http://blogs.msdn.com/b/nav/archive/2010/02/11/sql-server-trace-flag-4616-no-longer-required-for-dynamics-nav-2009-sp1.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+MicrosoftDynamicsNavTeamBlog+%28Microsoft+Dynamics+NAV+Team+Blog%29
trace flag 4616 is no longer required for NAV version 5.1 and above.
The only (logical) conclusion from these articles is for me that NAV client is using either a certificate-signed stored procedure in order to query the number of logins or that sp_unsetapprole is called to temporaliy disable the security context of $ndo$shadow and perform the query within the original server login context.
Indeed, if one takes a look at the things with SQL Profiler, the latter seems to be true.
But why is this not working in my constellation...- any ideas?