The client runs a NAV V4 database on V4.03 executables with a SQL2000 database back end.
They found that if they made their super users DBOwners on SQL, then the super users were no longer able to see other users on the database by going to the sessions in Database Information - Only their own session. This would happen to all users they configured this way and on different PCs.
When they took away the DBOwner permissions, things returned to normal and they could see other users listed.
Are you sure the DB is SQL 2000 and not 2005? We have seen this behaviour in 2005 and can resolve it by giving the user the securityadmin server role (or sysadmin, but that may be a bit drastic). Under 2005 it seems to be that db_owners in one DB no longer have permissions to view the server state (i.e. logins in all DB’s). It also works if you give the individual account access to view the server state.
Quite why it works for a user without db_owner is a bit strange, the difference seems to be that inside those users use the application role to access the relevant tables (session view in this case) and somehow it does have permissions to do this.
If you are on SQL server 2000 I have no idea TBH. Hope it helps a bit.
I’m afraid that this issue has come back. The main superuser experienced a SQL error on running a NAV backup, so their internal IT removed the SecurityAdmin roles from the users.
They then re-instated them to replicate the SQL error for investigation and to let the super users see the other users. However this does not seem to have worked, as the super users cannot see the other users again and the SQL error they reported does not occur…
I have logged this with MS, but any input would be very welcome.