Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / User access issue when...
Finance | Project Operations, Human Resources, ...
Answered

User access issue when restore the database

(0) ShareShare
ReportReport
Posted on by HanzSanaDeveloper 12

Hi everyone,

We have one server which has two GP instances and tenant services configured. 

GP services (SBA)  is configured with multi tenant configuration in same machine too.

So, each instance has its' own system and company databases.

For accompany one of our requirement I need to restore backup of one instance company database to the other instance.

once complete this task I was able to access GP by changing DBNAME of SY00100 table to correct system database.

But, After that, I'm experiencing few issues in user configuration. 

I'm able to create users, but when I'm trying to give company access to that user through 'User Access Setup' window, the operation doesn't commit. 

When I check database level I see, the record still hit into SY60100 table of system database of other instance which I took the backup.

 Is there any solution for this? or any configuration which I missed here?

 

  • Verified answer
    David Musgrave MVP GPUG All Star Legend Moderator Profile Picture
    David Musgrave MVP ... 14,042 Most Valuable Professional on at
    RE: User access issue when restore the database

    Running GP Power Tools: Database Validation would fix the problem.

    The issue is that the SQL Security IDs (SIDs) are different even if the User IDs themselves exist.

    Database Validation in GP Power Tools will recreate all the logins and database users based on the GP users and companies with encrypted passwords.

    It can even email the generated password to each user so they can change it on next login.

    David

  • Verified answer
    Beat Bucher GP Geek GPUG All Star Profile Picture
    Beat Bucher GP Gee... 28,058 Moderator on at
    RE: User access issue when restore the database

    Hi Manz,

    Have a look at this site.. Helped me quite a few times to fix SQL users account that had been restored as part of a company without having to delete them one by one (that is fine when you just have a handful, but not dozens)

    www.mssqltips.com/.../

    PS: and since you're talking about restore company DB's from one GP instance into another instance, I guess those system DB's had different names ? you may have to run the GP Maintenance Utility to rebuild all the SP's from this restored company, as some times the system DB name is hard-coded within the SQL SP's and point to the wrong name.. 

  • Suggested answer
    Justin Sutton Profile Picture
    Justin Sutton on at
    RE: User access issue when restore the database

    Hello Manz,

    Are the users you are attempting to add already in the database?

    Dynamics GP almost fully relies on SQL's user system which comes down to two levels: Database Users and Server Logins.

    For GP these two user groups are linked, with a single Server Login pointing to multiple Database Users.

    When you restore a company SQL Backup, the Database Users will come over. The Logins, however, are stored on the server level and will not come over.

    When you create a new user in GP it will create a SQL Login without issues, but when you add company access we cannot add a Database User because one already exists. Make sure to remove the user from each GP database manually (security folder under the database within SQL Server Management Studio) then try to add them back using the User Access window.

    This will occur any time a database is copied between servers. Even if there is a user with the same name, the unique IDs in the backend (SIDS) may not match, which could cause issues.

    Hopefully this is able to get your users in.

    Thanks!

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 601 Most Valuable Professional

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 416

#3
Adis Profile Picture

Adis 384 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans