Suddenly unable to login using Database Logins??

pdj

(NAV5SP1 - update 1 and SQL2005)
I have a customer who all of a sudden got problems logging in with his database users. The customer primarily uses Windows users, but for some reason a few users had to login as database users and is created as such on the SQL server. This has worked fine for a long time, but suddenly they started getting this error:
“You do not have the required SQL Server permissions to perform the current security activity.”
It turns out to be a result of a User.MODIFY (table 2000000002) which is executed in the LogInStart in Codeunit 1. The Modify is made to increment a custom field in the database. This used to work perfectly, and the field currently holds a value of 54697 :-)
I tried troubleshooting using the SQL Profiler, and found that the User.MODIFY results in a quite a few SQL statements. At first it seems as if the problem was because the user was neither a member of db_accessadmin or db_owner. However it didn’t help making him a db_owner – it just changed the SQL statements in the profiler. (??)

Can anyone see what the problem is, based on these examples from the SQL Profiler?

As Public:

* UPDATE "MyDbName"."dbo"."User" WITH (REPEATABLEREAD) SET "No_ of Logins with Password"=@P1 WHERE ("User ID"=@P2) AND ("timestamp"<=@P3) * 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 * SELECT [loginname] FROM [master].[dbo].[syslogins] WHERE [isntname] = 0 AND UPPER([loginname]) = @P1 * set nocount on * if @name_in_db is null * select @name_in_db = @loginame -- CHECK PERMISSIONS (Shiloh Check) -- * if (not is_member('db_accessadmin') = 1) and (not is_member('db_owner') = 1) * dbcc auditevent (109, 1, 0, @loginame, @TRANCOUNT > 0 ROLLBACK TRAN

As db_owner:

* UPDATE "MyDbName"."dbo"."User" WITH (REPEATABLEREAD) SET "No_ of Logins with Password"=@P1 WHERE ("User ID"=@P2) AND ("timestamp"<=@P3) * 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 * SELECT [loginname] FROM [master].[dbo].[syslogins] WHERE [isntname] = 0 AND UPPER([loginname]) = @trancount > 0) * if (@rolename is null) * select @encrStyle = case lower(@encrypt) when 'none' then 0 when 'odbc' then 1 else null end * if (@nestlevel > 1) * if (@fCreateCookie = 1) * setuser @rolename, @password, @TRANCOUNT > 0 COMMIT TRAN * SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED * set nocount on * if @name_in_db is null * select @name_in_db = @loginame -- CHECK PERMISSIONS (Shiloh Check) -- * if (not is_member('db_accessadmin') = 1) and (not is_member('db_owner') = 1) * dbcc auditevent (109, 1, 0, @loginame, @name_in_db, NULL, NULL, NULL, NULL, NULL) * raiserror(15247,-1,-1) * return (1)

Thanks in advance.

kriki

If you have the "Enhanced Security Model" in NAV, I would advice to first change it to "Standard Security Model". Probably it will solve the problem.
There are a lot of problems with the "Enhanced Security Model".

pdj

Hi Kriki, I’m using the Standard security model already – didn’t think anybody outside Microsoft actually used the other one :-)

Update! I suddenly discovered that I kept experimenting with different roles in the NAV database, where it probably should be the Master db instead. So I gave my test-user db_owner permission to the Master db, and now it worked like a charm! However; when I removed the db_owner permission again to the Master db, it kept working! I even created a new test-user again, and couldn’t reproduce the problem again. It seems NAV kept trying to fix something, which the Public role didn’t allow. Once I logged in using the db_owner permissions it fixed whatever was wrong, and now Public is enough again. My guess is that a new Database user wasn’t fully created or something… Now the SQL Profiler just shows these lines:

* UPDATE "MyDbName"."dbo"."User" WITH (REPEATABLEREAD) SET "No_ of Logins with Password"=@P1 WHERE ("User ID"=@P2) AND ("timestamp"<=@P3) * 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 * SELECT TOP 1 NULL FROM "MyDbName"."dbo"."Member Of" WITH (READUNCOMMITTED)

Strange...