Minimal security setting to see all sessions

DenSter · 2008-07-29

NAV 5.0, SQL Server 2005. My user is set up as db_owner of the NAV database only, and I can only see my own session in 'Current Sessions'. This has been discussed before, and I can make it so I see all sessions by giving the user 'serveradmin', but I am wondering if it is possible to do this without a server role.

I've tried various combinations of db_owner, access to system databases (master and temp and msdb and model) but without a server role I can't get it to work. Does anyone know the minimum security settings to get access to all user sessions without setting a server role?

garak · 2008-07-29

When the user opens the Database -> Information panel, navision (SQL Server) creates a temp. table only for the session. Does your customer has all read permissions to the selected views / systables (default is this role public) :?:

INSERT INTO     [#$ndo$sessions]                 SELECT          CAST(SP.[spid] AS INTEGER) AS "Connection ID",CAST(RTRIM(SP.[loginame]) AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS "User ID",CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS "My Session",CONVERT(DATETIME, '1754-01-01 '+CONVERT(CHAR(8), SP.[login_time], 108), 120) AS "Login Time",CONVERT(DATETIME, CONVERT(CHAR(10), SP.[login_time], 120)+' 00:00:00:000', 121) AS "Login Date",CAST (SD.[name] AS NVARCHAR(128)) COLLATE SQL_Latin1_General_CP850_CS_AS AS "Database Name",CAST (RTRIM(SP.[program_name]) AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS "Application Name",CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS "Login Type",CAST(RTRIM(SP.[hostname]) AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS "Host Name",SP.[cpu] AS "CPU Time (ms)",CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage]*8 END AS "Memory Usage (KB)",SP.[physical_io] AS "Physical I_O",CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS "Blocked",CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INTEGER) AS "Wait Time (ms)",CAST(SP.[blocked] AS INTEGER) AS "Blocking Connection ID",CAST(ISNULL(RTRIM(SPB.[loginame]), '') AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS "Blocking User ID",CAST(ISNULL(RTRIM(SPB.[hostname]), '') AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS "Blocking Host Name",CAST('' AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS "Blocking Object",CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT)*1000 ELSE 0 END AS "Idle Time" FROM [master].[dbo].[sysprocesses] AS SP                 JOIN            [master].[dbo].[sysdatabases] AS SD ON (SP.[dbid] = SD.[dbid])                 LEFT OUTER JOIN [master].[dbo].[sysprocesses] AS SPB ON (SP.[blocked] = SPB.[spid])                 WHERE           SP.[ecid] = 0 AND SP.[context_info] = 0x01 AND SP.[last_batch] >= CONVERT(DATETIME, '2000-01-01 00:00:00', 120)

when he open the sessionlist, nav / sql read the view session. These view reads the views: master.dbo.sysprocesses, master.dbo.sysdatabases.
Both views are in the role public (grant select)

SELECT     CAST(SP.spid AS INTEGER) AS [Connection ID], CAST(RTRIM(SP.loginame) AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS [User ID],
                       CAST(CASE WHEN SP.[spid] = @@SPID THEN 1 ELSE 0 END AS TINYINT) AS [My Session], CONVERT(DATETIME, '1754-01-01 ' + CONVERT(CHAR(8), 
                      SP.login_time, 108), 120) AS [Login Time], CONVERT(DATETIME, CONVERT(CHAR(10), SP.login_time, 120) + ' 00:00:00:000', 121) AS [Login Date], 
                      CAST(SD.name AS NVARCHAR(128)) COLLATE SQL_Latin1_General_CP850_CS_AS AS [Database Name], CAST(RTRIM(SP.program_name) 
                      AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS [Application Name], 
                      CASE WHEN SP.[nt_domain] <> '' THEN 1 ELSE 0 END AS [Login Type], CAST(RTRIM(SP.hostname) AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS
                       AS [Host Name], SP.cpu AS [CPU Time (ms)], CASE WHEN SP.[memusage] < 0 THEN 0 ELSE SP.[memusage] * 8 END AS [Memory Usage (KB)], 
                      SP.physical_io AS [Physical I_O], CAST(CASE WHEN SP.[blocked] <> 0 THEN 1 ELSE 0 END AS TINYINT) AS Blocked, 
                      CAST(CASE WHEN SP.[blocked] <> 0 THEN SP.[waittime] ELSE 0 END AS INTEGER) AS [Wait Time (ms)], CAST(SP.blocked AS INTEGER) 
                      AS [Blocking Connection ID], CAST(ISNULL(RTRIM(SPB.loginame), '') AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS [Blocking User ID],
                       CAST(ISNULL(RTRIM(SPB.hostname), '') AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS [Blocking Host Name], 
                      CAST('' AS NVARCHAR(64)) COLLATE SQL_Latin1_General_CP850_CS_AS AS [Blocking Object], 
                      CASE WHEN SP.[cmd] = 'AWAITING COMMAND' THEN CAST(DATEDIFF(SECOND, SP.[last_batch], GETDATE()) AS BIGINT) 
                      * 1000 ELSE 0 END AS [Idle Time]
FROM         master.dbo.sysprocesses AS SP INNER JOIN
                      master.dbo.sysdatabases AS SD ON SP.dbid = SD.dbid LEFT OUTER JOIN
                      master.dbo.sysprocesses AS SPB ON SP.blocked = SPB.spid
WHERE     (SP.ecid = 0)

Also, has your user the create table permission in tempdb :?:

kine · 2008-08-02

DenSter wrote:

NAV 5.0, SQL Server 2005. My user is set up as db_owner of the NAV database only, and I can only see my own session in 'Current Sessions'. This has been discussed before, and I can make it so I see all sessions by giving the user 'serveradmin', but I am wondering if it is possible to do this without a server role.

I've tried various combinations of db_owner, access to system databases (master and temp and msdb and model) but without a server role I can't get it to work. Does anyone know the minimum security settings to get access to all user sessions without setting a server role?

Just set the trace 4616 on the SQL and you are done...;-) or not?

DenSter · 2008-08-02

Nope that flag was already set. You can't even open NAV without setting that flag.

alesje · 2009-03-12

DenSter wrote:

Nope that flag was already set. You can't even open NAV without setting that flag.

We have same issue with one NAV Windows Login others on same server works fine.

After few tests which comnfirmed that it could be a SQL bug concerning trace flag T4616. We solved it with workarround SQL cmd: GRANT VIEW SERVER STATE TO PUBLIC enabling server wide permission just to view system tables without constraint on currently logged user.

petsch · 2009-06-25

In the navision role "All Users" you have to add tabledata 2000000009 session with read permission and after that synchronize all users.

I found that out after solving the issue why me technical update from3.60 DB to 5.01 SQL (SQL Server 2008) needed "db-owner" role for a normal user to login (enhanced security model). I matched my "all"-role with the entries from a 5.01 cronus db and after synchronizing it worked like a charme.

JosephGress · 2011-06-07

I ran into this issue of users not being able to see all sessions with a client that is switching from the native database to SQL. I was finally able to get this to work. I would like to share the steps that I used. These steps have been documented earlier by other MIBUSO contributors but I thought it might help someone to have them in one place. I am not certain that all of the steps are needed -- I kept going until I got it working.

This is for NAV2009 SP1.

1) Copy the session view from the Cronus sample database. I had to do a google search on how to copy a view in SQL to figure out how to do this. The procedure that I used was to Right click on the Cronus database and say Tasks->Generate scripts then copy and run the script on the live database (after first making a backup).

2) Run the query GRANT SELECT ON [SESSION] TO [PUBLIC] in the live NAV database.

3) Add Tabledata 20000009 Session with Read Permission to the ALL role in NAV security

4) Run the query GRANT VIEW SERVER STATE TO PUBLIC on the SQL Server instance where NAV is installed

5) Turn on trace flag 4616 on the SQL Server instance where NAV is installed. I had to look this up as well. The command that I used was DBCC TRACEON (4616,-1)

With these steps, I did not need to give the user the SYSADMIN role in SQL Server.

Thanks again everyone for helping me resolve this and other issues. I use MIBUSO nearly every day to solve the various puzzles that I run into with NAV programming, installation and setup.

kine · 2011-06-07

If you have used only DBCC TRACEON (4616,-1), the flag will be lost after server restart if I am correct... you need to set it as parameter to the sql when starting...

roqueagullo · 2012-10-09

In SQL SERVER 2008 R2 with Dynamics NAV 2009 R2, I just activated the trace flag 4616 set on SQL and it worked.

Minimal security setting to see all sessions

Comments

Categories