Restored GP 8.0 from server crash, users can't log in and SA oddit

The server that hosts GP 8.0 crashed, we had a backup and restored it.  Upon 
boot SQL 2000 was giving us issues, we had to reinstall it to get it running 
again.  The GP 8.0 DB's were reattached.  I can see the users in their 
respective DB's from Enterprise Manager but when I attempt to log in as said 
users, it gives the dreaded:

"Your SQL Server login record is not a valid user record in Great Plains.  
Enter a different login, or contact your Great Plains system administrator."

Right now the only account that lets me on is the SA but even so, whenever I 
attempt to do anything inside the Utilities - System, it prompts me for a 
password, I enter the SA password and it rejects it over and over.

Any thoughts on how I can work through this?
0
LV (8)
12/19/2008 7:07:02 PM
greatplains 29623 articles. 6 followers. Follow

3 Replies
557 Views

Similar Articles

[PageSpeed] 32

You have two major issues going on for you at this point: a) you are missing 
all your SQL Server logins and you are missing all your database security 
settings. The following solution assumes you have working backup copies of 
your database and that you are in a position to detach the existing DYNAMICS 
and company databases.

1) Run the following query against your DYNAMICS database:

-- capture users
SELECT USERID, USERNAME FROM SY01400
-- capture companies
SELECT CMPANYID, CMPNYNAM, INTERID FROM SY01500 ORDER BY CMPANYID
-- capture all users access to companies
SELECT A.USERID, B.USERNAME, C.CMPNYNAM FROM SY60100 A INNER JOIN SY01400 B 
ON A.USERID = B.USERID INNER JOIM SY01500 C ON A.CMPANYID = C.CMPANYID

Copy the results to NOTEPAD.

a)  BACKUP ALL DATABASES
b ) Proceed to drop ALL database users from the DYNAMICS and company 
database with Enterprise Manager.

2) Make a backup of your Dynamics and company databases then detach the 
DYNAMICS and company databases from SQL Server. Move (not copy/paste) the 
data and log files to a safe location, preferrably off the current server. 
Also, move your backup files to a safe location.

3) On your SQL Server, run the Dynamics Utilities again. Recreate the 
DYNAMICS database and company database with the INTERIDs in step 1. Also, by 
running the Dynamics Utiities you will recoup things like the DEX_LOCK and 
DEX_SESSION tables in the TEMPDB, the DYNGRP role which got wiped out by 
reinstalling SQL Server, the DYNSA user, which is typically the database 
owner of all DYNAMICS and company databases.

If you remember the account framework and account formats for each company, 
great. If not, don't worry, because when you restore the databases later on 
you will be fine.

4) Once all company databases have been created, then log into GP as sa and 
go to Setup > System > User and create each user ID (set all passwords to be 
the same, i.e., p@ssw0rd) with the list from step 1. Assign the users to the 
companies the had access to. Use the third result set from the queries above.

5) At this point I am inclined to have you use the CaptureLogin scripts 
provided with KB article 878449 ( 
https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;878449 ) 
to gather the logins created. Read through this article for instructions on 
executing this script and later on reapplying all the logins.

6) Restore your database backups from step 2.

7) Reapply your logins as indicated in KB article 878449 in step 5

8) Run the GRANT.SQL utility script found under the SQL directory of you 
server installation of GP. The SQL directory can be found under the GP 
installation folder.

This should complete the steps. Just post with any issues you may find.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com


"LV" wrote:

> The server that hosts GP 8.0 crashed, we had a backup and restored it.  Upon 
> boot SQL 2000 was giving us issues, we had to reinstall it to get it running 
> again.  The GP 8.0 DB's were reattached.  I can see the users in their 
> respective DB's from Enterprise Manager but when I attempt to log in as said 
> users, it gives the dreaded:
> 
> "Your SQL Server login record is not a valid user record in Great Plains.  
> Enter a different login, or contact your Great Plains system administrator."
> 
> Right now the only account that lets me on is the SA but even so, whenever I 
> attempt to do anything inside the Utilities - System, it prompts me for a 
> password, I enter the SA password and it rejects it over and over.
> 
> Any thoughts on how I can work through this?
0
MarianoGomez (3440)
12/19/2008 9:41:03 PM
I have always approached this different.  You should have recovered SQL 
logins before reloading SQL as that is where the logins are missing.

Anyway.  there are a couple of scripts in the SQL\Utilities folder under GP 
on the "server" install.  One is Grant and the other starts with an R....   
Run the R....??? script to create the locks and session tables in SQL

As for logins, login to GP with the SA password and one by one delete the 
users.  You will get errors but they will delete.  You can then recreate them 
with no errors and be ok.  There is a KB article that tells you how to 
recover logins but it recovers them from SQL and NOT from any GP db.  If you 
have already wiped out your original SQL install it is too late to run this.

-- 
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at 
http://www.AccoladePublications.com



"Mariano Gomez" wrote:

> You have two major issues going on for you at this point: a) you are missing 
> all your SQL Server logins and you are missing all your database security 
> settings. The following solution assumes you have working backup copies of 
> your database and that you are in a position to detach the existing DYNAMICS 
> and company databases.
> 
> 1) Run the following query against your DYNAMICS database:
> 
> -- capture users
> SELECT USERID, USERNAME FROM SY01400
> -- capture companies
> SELECT CMPANYID, CMPNYNAM, INTERID FROM SY01500 ORDER BY CMPANYID
> -- capture all users access to companies
> SELECT A.USERID, B.USERNAME, C.CMPNYNAM FROM SY60100 A INNER JOIN SY01400 B 
> ON A.USERID = B.USERID INNER JOIM SY01500 C ON A.CMPANYID = C.CMPANYID
> 
> Copy the results to NOTEPAD.
> 
> a)  BACKUP ALL DATABASES
> b ) Proceed to drop ALL database users from the DYNAMICS and company 
> database with Enterprise Manager.
> 
> 2) Make a backup of your Dynamics and company databases then detach the 
> DYNAMICS and company databases from SQL Server. Move (not copy/paste) the 
> data and log files to a safe location, preferrably off the current server. 
> Also, move your backup files to a safe location.
> 
> 3) On your SQL Server, run the Dynamics Utilities again. Recreate the 
> DYNAMICS database and company database with the INTERIDs in step 1. Also, by 
> running the Dynamics Utiities you will recoup things like the DEX_LOCK and 
> DEX_SESSION tables in the TEMPDB, the DYNGRP role which got wiped out by 
> reinstalling SQL Server, the DYNSA user, which is typically the database 
> owner of all DYNAMICS and company databases.
> 
> If you remember the account framework and account formats for each company, 
> great. If not, don't worry, because when you restore the databases later on 
> you will be fine.
> 
> 4) Once all company databases have been created, then log into GP as sa and 
> go to Setup > System > User and create each user ID (set all passwords to be 
> the same, i.e., p@ssw0rd) with the list from step 1. Assign the users to the 
> companies the had access to. Use the third result set from the queries above.
> 
> 5) At this point I am inclined to have you use the CaptureLogin scripts 
> provided with KB article 878449 ( 
> https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;en-us;878449 ) 
> to gather the logins created. Read through this article for instructions on 
> executing this script and later on reapplying all the logins.
> 
> 6) Restore your database backups from step 2.
> 
> 7) Reapply your logins as indicated in KB article 878449 in step 5
> 
> 8) Run the GRANT.SQL utility script found under the SQL directory of you 
> server installation of GP. The SQL directory can be found under the GP 
> installation folder.
> 
> This should complete the steps. Just post with any issues you may find.
> 
> Best regards,
> --
> MG.-
> Mariano Gomez, MIS, MCP, PMP
> Maximum Global Business, LLC
> http://www.maximumglobalbusiness.com
> The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
> 
> 
> "LV" wrote:
> 
> > The server that hosts GP 8.0 crashed, we had a backup and restored it.  Upon 
> > boot SQL 2000 was giving us issues, we had to reinstall it to get it running 
> > again.  The GP 8.0 DB's were reattached.  I can see the users in their 
> > respective DB's from Enterprise Manager but when I attempt to log in as said 
> > users, it gives the dreaded:
> > 
> > "Your SQL Server login record is not a valid user record in Great Plains.  
> > Enter a different login, or contact your Great Plains system administrator."
> > 
> > Right now the only account that lets me on is the SA but even so, whenever I 
> > attempt to do anything inside the Utilities - System, it prompts me for a 
> > password, I enter the SA password and it rejects it over and over.
> > 
> > Any thoughts on how I can work through this?
0
info4071 (3004)
12/20/2008 2:40:00 AM
I like to say that there are usually 3 ways to do the same thing with GP. 
:-)  So here is a possible 3rd approach:

Install the Professional Services Tools Library.  One of the free tools is a 
login generator that will recreate the SQL users based on what's in your GP 
databases.  It will assign random passwords that you can then either change 
yourself or have the users change.

Separately from that, when you are in GP and are asked for the system 
password, that is not the same thing as the 'sa' password (unless, of 
course, you made them the same on purpose).  This is a GP system password 
that is used to protect access to system setup windows.  If no one in your 
organization knows what the GP system password is, talk to either your GP 
partner or GP support about steps to recover it.  I don't believe those 
steps should be posted online for all to see.

-- 
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html
blog: www.victoriayudin.com


"LV" <LV@discussions.microsoft.com> wrote in message 
news:D5E0735B-11F6-40A8-A020-7237D21DAC0F@microsoft.com...
> The server that hosts GP 8.0 crashed, we had a backup and restored it. 
> Upon
> boot SQL 2000 was giving us issues, we had to reinstall it to get it 
> running
> again.  The GP 8.0 DB's were reattached.  I can see the users in their
> respective DB's from Enterprise Manager but when I attempt to log in as 
> said
> users, it gives the dreaded:
>
> "Your SQL Server login record is not a valid user record in Great Plains.
> Enter a different login, or contact your Great Plains system 
> administrator."
>
> Right now the only account that lets me on is the SA but even so, whenever 
> I
> attempt to do anything inside the Utilities - System, it prompts me for a
> password, I enter the SA password and it rejects it over and over.
>
> Any thoughts on how I can work through this? 

0
victoria (3340)
12/20/2008 9:44:18 AM
Reply:

Similar Artilces:

Port 25 telnet on Exchange 2K3 servers
Hi, Installed 3 new Exchange 2K3 SP2 for a client the other day, but unable to telnet to port 25, even on the servers them selves... the servers are 2 Enterprise & 1 standard 2K3 SP2, they also have Mcafee Antivirus ( file level with Exchange folder exclusion ) and Mcafee GroupShield on them... Curiously all email functions are ok, able to send and receive internally and to Internet, just not able to telnet to port 25 on any servers... If I add another port to the SMTP VS, then able to telnet that port, but not 25, so I think one of the Anti-virus programs is stopping port 25... ...

Store.exe crashing on 2 Exchange 2003 servers
We have 2 Exchange 2003 SP1 servers (Win 2K SP4) which have developed a problem with the store crashing every half hour or so. System event log: The Microsoft Exchange Information Store service terminated unexpectedly. It has done this 2 time(s). The following corrective action will be taken in 0 milliseconds: No action. Application event log Faulting application store.exe, version 6.5.7226.3, stamp 407db771, faulting module storevs2.dll, version 7.1.367.0, stamp 40746ce5, debug? 0, fault address 0x00002238. I think it is interesting that both servers are crashing, but not at exact...

Saving user account settings to a network folder
I have been able to set Outlook to look to a network folder to see the Outlook file that has the user's mail account set up in it. However, the customized signature and other program settings are not saved in this file (outlook.pst). Is there a way to save all of the customizations to the newtork as well (another file)????? Thank you in advance for your assistance. Hi Jonathan I recently had to upgrade from Ex5.5 to Ex2k and the only way I could do this was to export all the user data to a pst file and import it in to Ex2K. The only draw back was that all the things such as rules...

Exchange delayed delivery/NDR due to 421+Server+too+busy (greylist bug?)
Exchange 2003 SP2. I think we are seeing the exchange "greylisting" bug where emails sometimes get stuck and are not sent, nor are the users notified in a timely manner. In my case it is not greylisting but a 421+Server+too+busy error that exchange is not retrying on. Most of the time the emails to a particular client get through, it is the occasional one that does not. If I look at the message history in the message tracking cneter it shows "Message transferred to OTHER_MAILSERVER through SMTP" for the successful sends. For the failed sends it shows ""Me...

Return back to GP 9 from GP 10
I just upgrade to gp 10 in one of the work stations and I get some troubles so I retrieved to Gp 9 and I get this message (You are using an earlier version of dynamics.dic than the one that’s currently installed on the network) What I have to do? can I make restore for database? ...

CRM 3.0 Install Problems
We're trying to upgrade a dev instance of CRM 1.2 to 3.0. It gets partway through the installation before it fails, giving this error message: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. Execution of a full-text operation failed. The parameter is incorrect. Looking at the log, it looks like this is where it fails: 15:00:38| Info| Disable full-text indexing 15:00:44| Error| Install exception.System.Exception: Action Microsoft.Crm.Setup.Server.InstallDatabaseAction failed. ---> System.Data.SqlClient.SqlException: Execution of a full-text operation failed....

OWA 2003 Log Off Button gives HTTP 500 Internal Server Error
When Logging off of 2003 OWA I get a page cannot be displayed error message. Everything else seems to be working fine. There's a KB about this. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Warren" <Warren@discussions.microsoft.com> wrote in message news:97769784-4AA2-4445-A0AA-86E0581681A1@microsoft.com... > When Logging off of 2003 OWA I get a page cannot be displayed error > message. > Everything else seems to be working fine. Ed, I did a search in the Knowledge Base and found nothing!!!! Help! Warren ...

Exchange Server 2007 Public Folders Permission issue
We have a consultants folder with over a hundred sub folders. Is there any way to propagate the permissions down to all the sub folders? Get-PublicFolder \<Parent_Folder_Tree>\Consultants -Recurse | Add-PublicFolderClientPermission -AccessRights <see_help_for_optoins> -User <Domain\User> -- Ed Crowley MVP "There are seldom good technological solutions to behavioral problems." .. "Stuart Reicher" <StuartReicher@discussions.microsoft.com> wrote in message news:DF14A00D-D9E6-486D-9237-C7ED652524F5@microsoft.com... > We have a cons...

Sender ID doesn't works? Perf. counters = 0!
Hi, the sender ID feature appear to not work correctly on my server. the performance counters are allways = 0. Total message validated by sender id = 0 etc... I jhave followed all the instruction in the video: http://www.microsoft.com/exchange/default.mspx my SMTP virtual server is enabled, my server is enabled, but there is no changes. any idea? thanks. Jerome. ...

integration microsoft crm 4.0-sharepoint server 2007
Hello, I searched information about integration microsoft crm 4.0 with sharepoint server 2007, but I didn't find something, I don't know if I can integrate that.. Can you tell me about that? Thanks!! yorleni_jimenez@yahoo.com Hi, Yes, you can get both integrated. You mostly need to work on MOSS side. Following blog is on CRM 3 and MOSS but it will give you pretty good idea; http://rehmangul.wordpress.com/2007/05/08/ms-crm-and-sharepoint-2007-integration/ -- uMar Khan :: MS CRM MVP CRM Freelance Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MV...

Excel and Calendar Control 8.0
Enter an Excel worksheet. Click Insert, select Object and then, Calendar Control 8.0. Now, the Calendar is shown on the worksheet. And then, the worksheet operations is finished with the Calendar. Please say, how to remove the Calendar from the worksheet. Regards. ...

sql replication on GP 8.0
Hello, I was wondering if it is possible to setup replication on the Great Plains db's ie Dynamics and the company databases? If so which replication method does anyone suggest? Thanks in advance. Jake Jake- I have tried this before, and I don't think it's possible. The replication adds a column to every table (I forgot the name, but something like Replication ID). This messes with the stored procs that dexterity uses to read the tables. Regards, Brenner bklenzman@klenzmanconsulting.com "Jake" wrote: > Hello, > > I was wondering if it is...

Browser crashing
I am accessing CRM 1.2 from an IE 6.0 browser on an NT workstation client. The app loads perfectly and all forms can be opened without problems. However, there is an intermittent problem that causes the browser to hang - usually when a form is closed - no particular form. ANYONE OUT THERE RUNNING CRM BROWSER CLIENT ON NT WORKSTATION?? Try making sure theres no toolbars/spyware/adware,........those interfere greatly with IE. Try going to add/remove, and make sure no rogue toolbar/etc is installed, then get something like Adaware, update it, and do a scan..........then try CRM again also,...

Linked Servers / OLE DB Jet4.0 request fails!
Hello I tried to execute an sql statement throught SQL OLEDB command It fails since I moved my configuration on 64 bits Windows 2008 Server System and migrate Sql Server 2005 to 2008. I want to execute an SQL Statement into an Excel file with Sql Linked Servers OleDbConnection sq = new OleDbConnection(); sq.ConnectionString = "Provider=SQLOLEDB;Data Source=xxx;user id=xxx;password=xxx;"; [...] sql = "insert into openquery(... FROM [Export$]) ... OleDbCommand c = new OleDbCommand(sql, sq); c.ExecuteNonQuery(); Returns : can't execute the query, prov...

Move server
I have 2 NT 4.0 boxes running Exchange 5.5. I want to move all the Exchange site and mailbox info to the newer of the boxes. I know there is a white paper on this. Can someone point me in the right direction? Cannot seem to find it on my own. Thanks and happy holoiday Frank Frank, The first step would be to move all of the mailboxes to the newer server. The second step would be to decommission the older server. I have included a link to an article detailing how to remove the first server in a site. 152959 XADM: How to Remove the First Exchange Server in a Site http://support.micr...

Great Plain 9.0 Hang up
i, I upgrade to Great Plains 9.0 from 8.0. Everything seems OK, the only issue is Great Plains 9.0 looked hang-up when I log in one of companies as sa. In DEXSQL.LOG, I found the Great Plains run zDP_SOP10100SS_1 'Order Number', 3 zDP_RM00101SS_1 'Customer Number' zDP_IV40201SS_1 'EACH' zDP_IV00101SS_1 'Each Item in that order' Above stored procedure seems run on each order, and there is thousands orders in that company, I wonder how long it may take. In my case, the Great Plains is just hang there for hours till I kill the process So, i...

Moving SOMgr & Server
After installing POS,Mgr & server one one computer (and it works fine) I have moved the Server and Mgr to another computer on the network. The POS and dongole remain on original computer. When I go into the Mgr I get the 941 error about dondgle not being found. Is there something else I need to do so Mgr sees the dongle on POS machine? The POS station with the dongle needs to be Register #1 and needs to be the first machine to run RMS POS every day. Tom "NcS" wrote: > After installing POS,Mgr & server one one computer (and it works fine) I have > moved the Ser...

Problems with Excel and User Capabilities
When I try to set up user accounts with access to only certain programs I run into a problem. We have installed MS Office v.X and I allow the applications Word, Powerpoint, and Excel to run. Word and Powerpoint run fine, but I get an error when starting Excel. When I start Excel up a window pops up saying "A unexpected error occurred while trying to load the Mircosoft Framework X library." I can hit OK and it returns to the program where everything seems to function normally. Has anyone else encountered this or have any advice? In article <#YTu5dWcDHA.2416@TK2MSFTNGP10....

Looking for a company to manage a co lo'd Exchange 2003 server.
I have a client that has just deployed a Exchange 2003 system at a co lo and has quickly realized that it is more than they can handle. They have asked me to help them find a company to manage and support their email system for approx 200 users. Does anyone have any suggestions of companies that can handle this? TIA John if you google "hosted Exchange" you'll see lots of hits...probably need to look into a few to find the right fit for you, but it appears there are lots of options for this... -- Susan Conkey [MVP] "John" <nospam@hotmail.com> wr...

Spam sent from outside to our internal users, from our internal users addresses!!! E2k
I've seent this problem alot, but it hasn't haunted me for a while until today. Getting messages frmo users@mydomain.com, to users@mydomain.com. Using Exchange 2000, SP3 (+post fixes), on a W2k Sp4 box. I have the SMTP server settings to only allow to 10.x subnets to relay. This works if someone tries to relay to outside of us... but someone can send from any address to our domain, and I can't find any white papers that address this. I've checked 'Perform Reverse DNS on incoming messages", but this doesn't solve the problem. There has to be a way to specify that y...

Migration From 1.2 to 3.0
Dear all, We want to do a new installation of CRM3.0 on a new server. How to migrate data from 1.2 to 3.0 ? (1)Somebody suggested to use data migration tools. I don't like it because I have experienced before (when I migrate Goldmine to CRM 1.2). It is very headache and time-consuming. (2)Somebody suggested to do a new installation for CRM1.2 and upgrade to CRM3.0. Does it mean I can copy the database of 1.2 to a new server and install CRM1.2 ? I would actually first upgrade your existing system. Then look to migrating to the new hardware. With v3, the DB is a lot easier to mov...

move user
Hi, How can i move a user from C:\ to D:\ incl alle maps en picto's? thx in advance Slash Yes... but The full answer... Only files will function properly, like user pictures, music, favorites, documents. everything else will be broken. ...

Remote Access for CRM 3.0 Sales for Outlook
I'm using Outlook with RPC over HTTP and I'm looking for a good way to use CRM 3.0 for Laptops over the web. I'd like to avoid VPN at this stage and it's vital that these sync up often because we are using the service module heavily. I would also like to get it to run on PC's not joined to the domain. Does anyone have any tips for doing either of these? The app does not work with an RPC over HTTP connecion. There is more to it than just the Exchange connection as the app needs to communicate with the CRM Server as well. You might be able to figure it out, but ther...

Update Rollup 2 and Terminal Server
I've been using the terminstall.cmd script that MS put together for installing CRM 3 for Outlook on Terminal Server. How should I install the UR 2 on Terminal Server? I can't find any direction on this so any hints would be greatly appreciated! Thanks. Having the same issue myself. Can anyone point us to some suggestions? -Eric "NW" wrote: > I've been using the terminstall.cmd script that MS put together for > installing CRM 3 for Outlook on Terminal Server. > > How should I install the UR 2 on Terminal Server? > > I can't find any dir...

new user of excel
Is there a beginners newsgroup for excel? Thanks D scott Hi Donna, Yes, there is: Microsoft.public.excel.newusers Personally, I think this group will help you more: Microsoft.public.excel.worksheet.functions But do visit one of the many sites with useful links, like this one (just a random choice): http://www.jkp-ads.com/Links.htm -- Kind regards, Niek Otten Microsoft MVP - Excel "Donna" <djs@cogeco.ca> wrote in message news:OsjDK39UIHA.4360@TK2MSFTNGP06.phx.gbl... | Is there a beginners newsgroup for excel? | Thanks | D scott | | On Jan 10, 3:09=A0pm, "D...