April 28, 2010 at 12:08 pm
I upgraded my SQL server from SQL2000 to SQL2005 and migrated all of the users from the old server onto the new one. We have the new server setup to accept both DOMAIN and also SQL Server logins (mixed mode).
We are using the JDE ERP system and have a problem where the application needs to pass the SQL SERVER user name to the database in order to facilitate table operations. The application will pass the stored SQL user name and password to the sql server and then the operation to be completed. I am getting an error of the form:
[Microsoft][SQL Native Client][SQL Server]The server principal "CRPDTA" is not able to access the database "JDE_CRP" under the current security context.
this may be a JDE specific error, but I thought I would post it here in case anyone has any suggestions.
Currently, in SQL @005, the CRPDTA login name is authorized to LOGIN to the JDE_CRP database, but if I try to use those credentials, it refuses the login. I am confused as to why. Can anyone point me in the right direction?
Edit:
Further investigation shows that when viewing the SQL security for the user CRPDTA, the database JDE_CRP is not checked. But, if I look at the dtabase JDE_CRP, the user IS listed as the owner (expected). Since the system does not have this user as a registered user of the database, I cannot login with that user credentials.
I tried to add the database using the System Security utility, but it fails stating that the user already exists.
I then attempted to remove the user, but it has a schema attached to it and that schema has tables associated with it and I cannot remove either either the schema or user from the database.
Is there a way to fix this?
April 28, 2010 at 1:29 pm
How did you move the logins to the new server?
If you just created them manually, or ran a script that created them - then you probably have orphaned users.
Run the following to identify any orphaned users:
Execute sp_change_users_login 'Report';
If you get results, then try running the following on the users listed from above:
Execute sp_change_users_login 'Auto_Fix', '<username from above>';
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
April 28, 2010 at 1:38 pm
Jeffrey Williams-493691 (4/28/2010)
How did you move the logins to the new server?If you just created them manually, or ran a script that created them - then you probably have orphaned users.
Run the following to identify any orphaned users:
Execute sp_change_users_login 'Report';
If you get results, then try running the following on the users listed from above:
Execute sp_change_users_login 'Auto_Fix', '<username from above>';
That did the trick!!!
Thank you so much for the answer to the problem!
April 28, 2010 at 1:41 pm
You are welcome - thank you for the feedback.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply