mibuso.com

Microsoft Business Solutions online community
It is currently Wed Jun 19, 2013 4:06 pm

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 8 posts ] 
Author Message
 Post subject: [Solved] Suddenly unable to modify the User table in some databases?
PostPosted: Mon Aug 29, 2011 2:05 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 547
Location: Aalborg,Denmark
Country: Denmark (dk)
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???

_________________
Regards
Peter


Last edited by pdj on Tue Aug 30, 2011 12:26 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Suddenly unable to modify the User table in some databas
PostPosted: Mon Aug 29, 2011 3:30 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 547
Location: Aalborg,Denmark
Country: Denmark (dk)
I tried isolating the difference using the SQL Profiler, and found this statement:

Code: Select all
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?

_________________
Regards
Peter


Last edited by pdj on Tue Aug 30, 2011 8:55 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Suddenly unable to modify the User table in some databas
PostPosted: Mon Aug 29, 2011 6:59 pm 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7525
Location: Milan
Country: Italy (it)
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.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting


Top
 Profile  
 
 Post subject: Re: Suddenly unable to modify the User table in some databas
PostPosted: Mon Aug 29, 2011 7:29 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 547
Location: Aalborg,Denmark
Country: Denmark (dk)
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?

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: Suddenly unable to modify the User table in some databas
PostPosted: Tue Aug 30, 2011 10:16 am 
Online
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2013 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7525
Location: Milan
Country: Italy (it)
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.

_________________
Regards,Alain Krikilion
Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title! || Read Forum Rules before making a posting


Top
 Profile  
 
 Post subject: Re: Suddenly unable to modify the User table in some databas
PostPosted: Tue Aug 30, 2011 10:51 am 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 547
Location: Aalborg,Denmark
Country: Denmark (dk)
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:

_________________
Regards
Peter


Top
 Profile  
 
 Post subject: Re: Suddenly unable to modify the User table in some databas
PostPosted: Tue Aug 30, 2011 11:31 am 
Offline
Microsoft employee

Joined: Thu Oct 16, 2003 8:30 pm
Posts: 143
Location: MDCC, Vedbæk
Country: Denmark (dk)
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.

_________________
Dean McCrae - Senior Software Developer, NAV Server & Tools

This posting is provided "AS IS" with no warranties, and confers no rights.


Top
 Profile  
 
 Post subject: Re: Suddenly unable to modify the User table in some databas
PostPosted: Tue Aug 30, 2011 12:25 pm 
Offline

Joined: Wed Nov 24, 1999 8:01 am
Posts: 547
Location: Aalborg,Denmark
Country: Denmark (dk)
\: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:

_________________
Regards
Peter


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 8 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: