Suddenly unable to modify the User table in some databases?

pdj

A customer with NAV5SP1 and a SQL2008R2 server with multiple NAV databases. (1 production and multiple test dbs). All using Standard Security and mainly Windows logins and a few database logins.

As part of LogInStart() in Codeunit 1, we have added code to increment "No. of Logins", which is a custom field in the User table. This has worked for years, but suddenly gives an error to all Database logins in two of the databases: "You do not have the required SQL Server permissions to perform the current security activity."

I have compared the user permissions in NAV, but they are SUPER in both a working and non-working database.
I have compared the permissions in SSMS for the SQL User, but the user doesn't have any Database roles assigned.
I have checked the permissions in SSMS for the SQL Login, and it is just Public for all databases.

We have not changed anything. The customer has not changed anything. However; we fear the hosting center might have changed something - like enforcing a policy in some of the databases, we just can't find it...

Any idea or how to troubleshoot this further???

pdj

I tried isolating the difference using the SQL Profiler, and found this statement:

SELECT U."User ID" AS [security user], 
  LSU.[loginname], 
  UPPER(LSU.[loginname]), 
  SU.[name] AS [dbuser]        
FROM ([master].[dbo].[syslogins] AS LSU
  JOIN [dbo].[sysusers] AS SU 
    ON LSU.[sid] = SU.[sid] AND LSU.[isntname] = 0)       
  FULL OUTER JOIN "User" AS U 
    ON UPPER(LSU.[loginname]) = U."User ID"  
COLLATE Danish_Norwegian_CI_AS       
WHERE U."User ID" IS NULL OR LSU.[loginname] IS NULL

I tried this statement in a Query window with the DB login, but I got this error:
Msg 229...
The SELECT permission was denied on the object 'User', database ..., schema 'dbo'

I don't want to change that, so I tried with a Windows user instead.
For the working databases it returns one row:
security user, loginname, <no column name>, dbuser
NULL, sa, SA, dbo

For the non-working databases it returns two rows:
security user, loginname, <no column name>, dbuser
NULL, sa, SA, dbo
SOMEADM, NULL, NULL, NULL

Can anyone decode what this means?

kriki

A wild guess:
Have you enhanced security model?
In this case, it is better to use the standard security model to fix all those problems. Or try synchronize to fix this one.

pdj

kriki wrote:

Have you enhanced security model?

No, they are using the Standard security model for all the databases.
I have also synchronized, as it has helped me at other sites with strange problems. But not this one :-(

Any other suggestions?

kriki

The only thing I can come up with now is this: Each database has an application role "$ndo$shadow" (in SSMS:database=>Security=>Roles=>Application Roles).

In that role, table "User" should have these permissions:
Grant:Delete,Insert,References,Select,Update.

If you don't have that role, you should install the xp_ndo dll on the DB.

pdj

These permissions are also identical.

I even tried this statement:
EXEC sp_helprotect @name = 'User'

It gives the same result in both databases, and it is only the $ndo$shadow that has any permissions to the User table, and they are all Grant.

We are now disabling the code, so we can set the database users back to Public... :whistle:

dmccrae

Here is one theory about what is going on.

Your custom change to modify the User table is causing a platform trigger to run that performs a 'mini' synchronization task, for the Standard security mode. The intention of this sync is to ensure that the NAV users are in sync with the SQL users in the database and their related logins in the server (for the Enhanced model, as you know, it also syncs permissions).

The query you ran indicates that the SOMEADM user exists in the NAV User table, but not in either the database (in dbo.sysusers) or in the server (master.dbo.syslogins) - since you are getting NULLs in fields from those tables as a result of the joins. Somehow this users has become stray.

What the sync process will do is try to create the user in the database that only exists in NAV, by first finding a server login with the same name and then creating the user. The user that is running this code at the time though, does not have permission to create users, hence the permission error during the sync process.

To avoid the sync process taking any action try one of the following in SSMS (not NAV):

- Delete the SOMEADM user from the NAV User table.
or
- Create the SOMEADM user in the database as a SQL user, linked to a server login with the same name.

The query you ran on the 'good' database did not return such a row - it only returned a row with SA being found in SQL but not in NAV. The sync process would nornally try to delete such as user from SQL (because it is not in NAV) but for SA of course it skips this.

I hope this helps.

pdj

\:D/ THANK YOU DEAN! \:D/

I still don't know how it happend, but this solved the problem.

The database user SOMEADM were marked "Without login" for some reason. I then first tried to modify the User table with a Windows user with db_owner access. I thought this would correct the problem automatically, but that didn't fix anything, just like the "full" Syncronize all Login didn't help. I then deleted the SOMEADM database user, and re-created it from the SQL-Login.

Now everything works like a charm. :thumbsup: