"Stored Procedure smCleanupFilesBeforeLogin returned the following results" Error message when you try to sign in to a company in Microsoft Great Plains or in Microsoft Dynamics GP
This article provides a solution to an error that occurs when you try to sign in to a company in Microsoft Great Plains or in Microsoft Dynamics GP.
Applies to: Microsoft Dynamics GP
Original KB number: 849708
Symptoms
When you try to sign in to a company in Microsoft Dynamics GP or in Microsoft Business Solutions - Great Plains, you receive the following error message:
Stored Procedure smCleanupFilesBeforeLogin returned the following results: DBMS: XXX, Great Plains: XXX
Cause
This problem may occur for any one of the following causes:
- Cause 1: The smCleanupFilesBeforeLogin stored procedure is damaged or missing. See Resolution 1.
- Cause 2: The DYNSA user isn't the database owner of the company database. See Resolution 2.
- Cause 3: The permissions for Microsoft Great Plains users who are part of the DYNGRP role in Microsoft SQL Server are incorrect. See Resolution 3.
- Cause 4: Records are stuck in the ACTIVITY table or in the SY00800 table. See Resolution 4.
Resolution 1
Note
Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
Start SQL Server Management Studio or Query Analyzer. To do it, follow the steps for the program that you use.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
Select Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then select SQL Server Management Studio.
Note
The Connect to Server window opens.
In the Server name box, type the name of the instance of SQL Server.
In the Authentication list, select SQL Authentication.
In the User name box, type sa.
In the Password box, type the password for the sa user, and then select Connect.
- Microsoft SQL Server 2000
- Select Start, point to All Programs, point to Microsoft SQL Server, and then select Query Analyzer.
- Type the password for the sa user, and then select OK.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
On the File menu, select New.
Select Database Engine Query.
Re-create the stored procedure. Then, run the script against the company database. The following files are available for download from the Microsoft Dynamics File Exchange Server.
Microsoft scanned these files for viruses. Microsoft used the most current virus-detection software that was available on the date that these files were posted. The files are stored on security-enhanced servers that help prevent any unauthorized changes to the files.
Resolution 2
Note
Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
Start SQL Server Management Studio or Query Analyzer. To do it, follow the steps for the program that you use.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
Select Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then select SQL Server Management Studio.
Note
The Connect to Server window opens.
In the Server name box, type the name of the instance of SQL Server.
In the Authentication list, select SQL Authentication.
In the User name box, type sa.
In the Password box, type the password for the sa user, and then select Connect.
- Microsoft SQL Server 2000
- Select Start, point to All Programs, point to Microsoft SQL Server, and then select Query Analyzer.
- Type the password for the sa user, and then select OK.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
On the File menu, select New.
Select Database Engine Query.
Use the following script to determine the database owner.
sp_helpdb
If the DYNSA user isn't listed as the database owner (db_owner) in the results, make the DYNSA user the owner of the database. To do it, run the following script against the wanted database.
sp_changedbowner 'DYNSA'
Resolution 3
Note
Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
Start SQL Server Management Studio or Query Analyzer. To do it, follow the steps for the program that you use.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
Select Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then select SQL Server Management Studio.
Note
The Connect to Server window opens.
In the Server name box, type the name of the instance of SQL Server.
In the Authentication list, select SQL Authentication.
In the User name box, type sa.
In the Password box, type the password for the sa user, and then select Connect.
- Microsoft SQL Server 2000
- Select Start, point to All Programs, point to Microsoft SQL Server, and then select Query Analyzer.
- Type the password for the sa user, and then select OK.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
On the File menu, select New.
Select Database Engine Query.
Run the following Grant.sql script against the DYNAMICS database and against all the company databases. This script gives the appropriate permissions to all Microsoft Great Plains users who are part of the DYNGRP role in SQL Server.
/*Count : 1 */ declare @cStatement varchar(255) declare G_cursor CURSOR for select 'grant select,update,insert,delete on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects where (type = 'U' or type = 'V') and uid = 1 set nocount on OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> -1) begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatement end DEALLOCATE G_cursor declare G_cursor CURSOR for select 'grant execute on [' + convert(varchar(64),name) + '] to DYNGRP' from sysobjects where type = 'P' set nocount on OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> -1) begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatement end DEALLOCATE G_cursor
Resolution 4
Note
Make sure that all users are logged off Microsoft Dynamics GP or Microsoft Great Plains before you follow these steps.
Start SQL Server Management Studio or Query Analyzer. To do it, follow the steps for the program that you use.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
Select Start, point to All Programs, point to Microsoft SQL Server 2008 or to Microsoft SQL Server 2005, and then select SQL Server Management Studio.
Note
The Connect to Server window opens.
In the Server name box, type the name of the instance of SQL Server.
In the Authentication list, select SQL Authentication.
In the User name box, type sa.
In the Password box, type the password for the sa user, and then select Connect.
- Microsoft SQL Server 2000
- Select Start, point to All Programs, point to Microsoft SQL Server, and then select Query Analyzer.
- Type the password for the sa user, and then select OK.
- Microsoft SQL Server 2005, Microsoft SQL Server 2005 Express, or Microsoft SQL Server 2008
On the File menu, select New.
Select Database Engine Query.
Run the following script to remove any records that are stuck in the ACTIVITY table and in the BATCH ACTIVITY table.
DELETE DYNAMICS..ACTIVITY DELETE DYNAMICS..SY00800
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for