I have an especially strange issue with a client.
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.
Has anyone encountered this issue before?
Been a while so to the best of my recollection…
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 will double check which version of SQL the client is using and report back if this works.
You were correct - The client was using SQL2005 and this did fix the issue.
Great, glad I could help! After all, you are almost next door [;)]
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.
If you execute this TSQL (within context of your NAV database) …
GRANT SELECT ON [Session] TO PUBLIC
… does this help?