mibuso.com

Microsoft Business Solutions online community
It is currently Thu Oct 23, 2014 7:11 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 4 posts ] 
Author Message
 Post subject: Restore GP backup from SQL
PostPosted: Wed Jan 30, 2008 9:46 am 
Offline

Joined: Wed Nov 17, 2004 3:13 pm
Posts: 37
Location: Beirut
Country: Lebanon (lb)
Dear all,
I have a customer who took his last backup from SQL server 2000.
the server is down, and he get a new server. the server name is different now and he installed again the sql server 2000 (also different name from the old one). He wants to work again on GP.
What we must do in this case to be able to restore the database of GP successfully. I tried to install gp and restore the database of GP from SQL server 2000 but it's not working due to problem with user logging...
What is the best way to restore SQL backup ,specially taking into consideration the problem of users.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jan 31, 2008 3:05 pm 
Offline
Moderator
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Wed Jul 02, 2003 10:13 am
Posts: 7701
Location: Milan
Country: Italy (it)
I don't know anything about GP, but I suppose you just need to recreate the users in SQL.
Check this url (http://support.microsoft.com/kb/246133) to how to copy the logins from the old system.

_________________
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

»»» Mark your calendar: NAV TechDays 2014 - 20 & 21 November 2014 ««« Visit the conference website: http://www.navtechdays.com


Top
 Profile  
 
 Post subject:
PostPosted: Thu Feb 07, 2008 2:10 pm 
Offline

Joined: Fri Jan 25, 2008 2:43 pm
Posts: 6
Location: Stockholm
Country: Sweden (se)
I don't know GP either but I do know SQL Server 2000-2005 and NAV

If GP is using SQL Server built-in authentication of users, and I'm talking SQL logins now, not windows logins, one possible solution would be the following steps (works for Nav restore):

1. Restore the GP database
2. Recreate the SQL Logins that are missing
Note: SQL Login and Database User is not the same thing
The login will get a default database that is not GP. At this point it is not possible to set GP as the default database for this login since the user would exist already in GP.
3. Use the stored procedure in SQL Server: sp_change_users_login to remap a Database User with its SQL Login.
Sample: Get a list of database users that has no mapping, EXEC sp_change_users_login 'Report' -- Must be run in the GP database
Sample: Map the login Sam to the database user Sam, EXEC sp_change_users_login 'Update_One', 'Sam', 'Sam' -- Must be run in the GP database

After mapping, the login will have all the proper rights it had in the GP database at the time of backup.

For Nav, I usually make sure to first remap a user with SUPER right and then try to connect just to make sure the method will work. Other circumstances might require another or a modified solution.

The reason SQL Logins need to be mapped is that the login/user is not the key used to connect a login to a database, instead SQL Server uses GUIDs for that. So, when a database is backed up and restored on another instance of SQL server, the username might be the same as the login, but they are not the same since the GUIDs are different, thus the need for sp_change_users_login. sp_change_users_login updates the sysusers systemtable in the affected database to have the proper GUID.

When transferring users from once SQL instance to another, the GUIDs are also transferred along with passwords.

_________________
Beautiful things can be performed using XSLT/XPATH/XML, Yes, I'm a geek


Top
 Profile  
 
 Post subject: Restore Backup from SQL
PostPosted: Mon Feb 11, 2008 9:55 am 
Offline

Joined: Wed Nov 17, 2004 3:13 pm
Posts: 37
Location: Beirut
Country: Lebanon (lb)
Thanks guys for your help. I let the customer recreate users from GP and it works fine...


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

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


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: