Upgrade Progress Script v2

A few years ago I created a script (version 1) to see how far along Dynamics GP Utilities was in the process of upgrading company databases. If you've ever upgraded GP before you know the upgrade screen doesn't always refresh nor does it give any real indication of time. With the arrival of GP 2015, I needed to update my original script to work correctly. I figured it was also about time to give the script a major face-lift.

This script will now also take into account simultaneous company upgrades - using multiple GP clients to upgrade the company databases. (Tip: If you have a lot of GP companies to upgrade - or several large ones - using multiple clients to upgrade a subset of the companies can be a great way to reduce the upgrade time without affecting performance too much, providing the disk subsystem on the server can handle it.)

The estimated remaining time is based on the average completion time of companies that have completed so far. If you have only one GP company then this metric is pretty useless to you. If you have a few very small companies that already upgraded and the last one is your main company, the estimated time may be off by quite a bit, but you will still have some sort of idea how long it will take.

Special thanks to Lance Brigham for the upgrade statuses.

USE [DYNAMICS]
DECLARE @NewVersion int
DECLARE @NewBuild int

SELECT
    @NewVersion = db_verMajor,
    @NewBuild = db_verBuild
FROM
    DB_Upgrade
WHERE
    PRODID = 0
    AND db_name = 'DYNAMICS'

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END

SELECT * 
INTO #Completed_List
FROM (
    SELECT
        Completed_Company = RTRIM(db_name),
        Started_At = (SELECT TOP 1 start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0),
        Ended_At = CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN GETDATE() ELSE MAX(stop_time) END,
        Run_Time = 
            CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN
                GETDATE()-(SELECT TOP 1 start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            ELSE
                MAX(stop_time)-(SELECT TOP 1 start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            END,
        Upgrading = CASE 
            WHEN EXISTS (select top 1 * from DU000030 DU INNER JOIN SY01500 SY on DU.companyID = SY.CMPANYID WHERE DU.Status NOT IN (0,15) AND DU.errornum <> 0 AND SY.INTERID = DBU.db_name) 
                THEN -1 
            WHEN MAX(db_status) NOT IN (0,15) AND NOT EXISTS (SELECT * FROM duLCK L WHERE L.INTERID = DBU.db_name) 
                THEN -1 
            WHEN MAX(db_status) NOT IN (0,15) 
                THEN 1 
            WHEN EXISTS (SELECT * FROM duLCK L WHERE L.INTERID = DBU.db_name) 
                THEN 2 
            ELSE 0 
        END,
        Status = db_status
    FROM
        DB_Upgrade DBU
    WHERE
        (
            EXISTS(
                select *
                from DB_Upgrade where
                db_verMajor = @NewVersion
                and db_verBuild = @NewBuild 
                AND PRODID =  0
                AND db_name <> 'DYNAMICS'
                and db_name = DBU.db_name
            )
        )
        AND PRODID = 0
    GROUP BY db_name, db_status
    UNION
    SELECT
        Completed_Company = RTRIM(db_name),
        Started_At = NULL,
        Ended_At = NULL,
        Run_Time = 0,
        Upgrading = 2,
        Status = db_status
    FROM
        DB_Upgrade DBU
    WHERE
        EXISTS (
            select *
            from duLCK where
            DBU.db_name = duLCK.INTERID
        )
        AND PRODID = 0
        AND db_status = 0
    GROUP BY db_name, db_status
) a


SELECT
    Status = CASE Upgrading WHEN 2 THEN 'Pending' WHEN -1 THEN 'Failed' WHEN 1 THEN 'Upgrading' ELSE 'Completed' END,
    Company = CL.Completed_Company,
    [Last Step Completed] = CASE Status
            WHEN 0 THEN ''
            WHEN 1 THEN 'Step 1/59: Upgrade started'
            WHEN 2 THEN 'Step 2/59: Defaults loaded'
            WHEN 3 THEN 'Step 3/59: Tables created'
            WHEN 4 THEN 'Step 4/59: Indexes created'
            WHEN 5 THEN 'Step 5/59: Views created'
            WHEN 6 THEN 'Step 6/59: Dexterity procs created'
            WHEN 7 THEN 'Step 7/59: Changed Dexterity procs dropped'
            WHEN 8 THEN 'Step 8/59: Changed application procs dropped'
            WHEN 9 THEN 'Step 9/59: Changed indexes dropped'
            WHEN 10 THEN 'Step 10/59: Changed views dropped'
            WHEN 11 THEN 'Step 11/59: Changed triggers dropped'
            WHEN 12 THEN 'Step 12/59: Changed rules dropped'
            WHEN 13 THEN 'Step 13/59: Changed tables dropped'
            WHEN 14 THEN 'Step 14/59: SQL code updates ran'
            WHEN 15 THEN 'Step 15/59: New tables added'
            WHEN 16 THEN 'Step 16/59: Loading table stored procedures'
            WHEN 17 THEN 'Step 17/59: Loading required data'
            WHEN 21 THEN 'Step 21/59: Existing data conversion process started'
            WHEN 23 THEN 'Step 23/59: Existing data conversion process checkpoint'
            WHEN 30 THEN 'Step 30/59: Existing data conversion process completed'
            WHEN 41 THEN 'Step 41/59: New views added'
            WHEN 42 THEN 'Step 42/59: New triggers added'
            WHEN 43 THEN 'Step 43/59: Rules created'
            WHEN 44 THEN 'Step 44/59: Stubs created'
            WHEN 45 THEN 'Step 45/59: Misc stored procs created'
            WHEN 46 THEN 'Step 46/59: FRx data created'
            WHEN 47 THEN 'Step 47/59: Permissions script ran'
            WHEN 48 THEN 'Step 48/59: Table defaults bound'
            WHEN 49 THEN 'Step 49/59: Procs recompiled'
            WHEN 53 THEN 'Step 53/59: Functions loaded'
            WHEN 54 THEN 'Step 54/59: Application stored procs created, running misc scripts now'
            ELSE 'Step ' + CAST(Status as CHAR(2)) + '/59'
            END,
    Pending = CASE WHEN Upgrading = 1 THEN (select COUNT(*) FROM duLCK WHERE NOT EXISTS (SELECT * FROM #Completed_List AS c INNER JOIN duLCK as l ON c.Completed_Company = l.INTERID WHERE upgrading = 1 and INTERID = duLCK.INTERID) ) ELSE NULL END,
    [Not Upgraded] = CASE WHEN Upgrading = 1 THEN (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') ELSE NULL END,
    Upgraded = CASE WHEN Upgrading = 1 THEN (select COUNT(*) from DB_Upgrade WHERE (db_verMajor = @NewVersion AND db_verBuild = @NewBuild AND db_verOldMajor = db_verMajor AND db_verOldBuild = db_verBuild)  and PRODID = 0 and db_name <> 'DYNAMICS') ELSE NULL END,
    [Avg Time Per DB] = CASE WHEN Upgrading = 1 THEN
        CONVERT(varchar(4),DATEPART(DAY,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))-1) + 'd ' +
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 's '
        ELSE '' END,
    [Est. Remaining Time] = CASE WHEN Upgrading = 1 THEN
        CONVERT(varchar(4),DATEPART(DAY,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))-1) + 'd ' +
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 's '
        ELSE '' END,
    [Estimated End Time] = CASE WHEN Upgrading = 1 THEN
        CONVERT(varchar(20),
            GETDATE() +
            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * 
                (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') 
            as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
            +
            (
                (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                (SELECT TOP 1 Run_Time FROM #Completed_List WHERE Upgrading = 1)
            )
        )
    ELSE '' END
    ,
    [Elapsed Time] = CASE WHEN Upgrading = 1 THEN
        CONVERT(varchar(4),DATEPART(DAY,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))-1) + 'd ' +
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 's '
        ELSE '' END
    FROM #Completed_List CL
    WHERE Upgrading <> 0
    ORDER BY Status DESC, Company 

SELECT
        [Completed Company] = CASE WHEN Upgrading = 1 THEN Completed_Company + ' - Upgrading' WHEN Upgrading = -1 THEN Completed_Company + ' - Failed' ELSE Completed_Company END,
        [Completed Company Name] = RTRIM(CMPNYNAM),
        [Started At] = Started_At,
        [Ended At] = CASE WHEN Upgrading = -1 THEN Started_At ELSE Ended_At END,
        [Run Time] = 
            CASE 
                WHEN Upgrading = -1 THEN '0h 0m 0s'
                ELSE
                    CONVERT(varchar(4),DATEPART(DAY,(Run_Time))-1) + 'd ' +
                    CONVERT(varchar(4),DATEPART(HOUR,(Run_Time))) + 'h ' +
                    CONVERT(varchar(4),DATEPART(MINUTE,(Run_Time))) + 'm ' +
                    CONVERT(varchar(4),DATEPART(SECOND,(Run_Time))) + 's '
            END
FROM
    #Completed_List
LEFT JOIN
    SY01500 ON #Completed_List.Completed_Company = SY01500.INTERID
WHERE
    Upgrading <> 2
ORDER BY 
    Upgrading DESC, 
    [Ended At] DESC

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END

eConnect error - Name cannot begin with the 'x' character.

I just ran into a rather interesting error I'd never seen before in Integration Manager. This integration uses the eConnect destination adapter going into Purchase Orders.

The error we received was as follows:

 eConnect error - Name cannot begin with the '6' character, hexidecimal value 0x36. Line 1, position 12559.

Integration Manager / eConnect creates the XML code automatically, so how would we even troubleshoot this?

I found a reference to an article on PartnerSource for saving the XML files IM/eConnect creates to a folder for troubleshooting, but the link was down and I couldn't find reference to it anywhere else on the internet (of course). See this page for a little more about the link (scroll down to the "Creating XML Data Files") section.

Looking at the data file and the error a few more times, I had a hunch there may be something wrong in the parsing of the import data so I looked through the text file for a '6' with a special character nearby. I found a 6 with the "less than" sign. Apparently Integration Manager 10 / eConnect couldn't handle this very well. I had the customer remove the "less than" sign, and all came in just fine.

Here is a screenshot from the import file (most of it is obscured for privacy reasons):

GP User Activity with Status and IP

If you've ever needed to clear out old GP users, you know it can sometimes be difficult to figure out which ones are still connected and which ones are not. Yes, the ones that logged in two days ago may seem the obvious choices to kick out, but that's not always the case. We've seen users leave GP open for days on end to not lose their seat and their SQL session is hot, and we've seen users open GP in the morning but bombed out an hour later but didn't need it anymore so they didn't bother to log in to clear their seat.

Either way, this script below combines several GP and SQL system tables and views to give you a broad view of the status of your GP users. It will show whether users in User Activity are in fact still connected to GP, how long they've been logged into GP, when their last SQL activity occurred and how long ago it was, their computer's IP address, if they are working on batches, and what table they are currently working in.

I'm sure there is still some room for improvement, and I will be adding to it from time to time, so check back later for any updates.

USE DYNAMICS
SELECT 
    Activity.USERID UserID, 
    Users.USERNAME Username, 
    Activity.CMPNYNAM Company,
    CASE
        WHEN AbandonedSessions.SQLSESID IS NULL
            THEN 'Connected'
        ELSE 'Removable'
    END Status,
    CONVERT(varchar(20),ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) LoggedInAt,
    ISNULL(CONVERT(varchar(20),ConnectedSessions.last_batch),'') LastGPUsage,
    CASE WHEN GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)) < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))-1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-(ISNULL(ConnectedSessions.last_login,Activity.LOGINDAT + Activity.LOGINTIM)))) + 's '
    END    TimeSinceLogin,
    CASE 
        WHEN ConnectedSessions.last_batch IS NULL 
            THEN ''
        WHEN GETDATE()-ConnectedSessions.last_batch < 0
            THEN '0d 0h 0m 0s'
        ELSE
            CONVERT(varchar(10),DAY(GETDATE()-ConnectedSessions.last_batch)- 1) + 'd '
            + CONVERT(varchar(10),DATEPART(HOUR,GETDATE()-ConnectedSessions.last_batch)) + 'h '
            + CONVERT(varchar(10),DATEPART(MINUTE,GETDATE()-ConnectedSessions.last_batch)) + 'm '
            + CONVERT(varchar(10),DATEPART(SECOND,GETDATE()-ConnectedSessions.last_batch)) + 's '
    END GPIdleTime,
    ISNULL(lock.table_path_name,'') OpenTable,
    ISNULL(batch.BatchCount,0) BatchesInProgress,
    Activity.SQLSESID SessionID,
    ISNULL(CONVERT(varchar(10),ConnectedSessions.SPID),'') SPID,
    ISNULL(ConnectedSessions.client_net_address,'') IPAddress
FROM 
    DYNAMICS..ACTIVITY Activity
    INNER JOIN DYNAMICS..SY01400 Users 
    ON Activity.USERID = Users.USERID
LEFT JOIN
    (
        SELECT *
        FROM DYNAMICS..Activity 
        WHERE SQLSESID NOT IN (
            SELECT SQLSESID
            FROM DYNAMICS..Activity A
            INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
            INNER JOIN master..sysprocesses P
            on S.sqlsvr_spid = P.spid
            AND A.USERID = P.loginame
        )
        AND NOT EXISTS
            (SELECT spid, c.CMPNYNAM DB, loginame,*
            FROM master..sysprocesses p
            inner join master..sysdatabases d
                on p.dbid = d.dbid
            inner join DYNAMICS..SY01500 c
                on d.name = RTRIM(c.INTERID)
            where c.CMPNYNAM = Activity.CMPNYNAM
                and p.loginame = Activity.USERID
                and p.program_name = ''
            )
    ) AbandonedSessions
    ON Activity.SQLSESID = AbandonedSessions.SQLSESID
    AND Activity.USERID = AbandonedSessions.USERID
LEFT JOIN
    (
        SELECT 
            A.SQLSESID, 
            P.spid, 
            P.last_batch, 
            ec.client_net_address, 
            CASE 
                WHEN P.login_time < A.LOGINDAT + A.LOGINTIM 
                    THEN p.login_time 
                ELSE A.LOGINDAT + A.LOGINTIM 
            END last_login
        FROM DYNAMICS..Activity A
        INNER JOIN tempdb..DEX_SESSION S
            ON A.SQLSESID = S.session_id
        INNER JOIN master..sysprocesses P
            ON S.sqlsvr_spid = P.spid
        AND A.USERID = P.loginame
        INNER JOIN sys.dm_exec_connections ec
            ON S.sqlsvr_spid = ec.session_id
    ) ConnectedSessions
    ON Activity.SQLSESID = ConnectedSessions.SQLSESID
LEFT JOIN
    (
        SELECT session_id, max(table_path_name) table_path_name
        FROM tempdb..DEX_LOCK
        GROUP BY session_id
    ) lock
    ON Activity.SQLSESID = lock.session_id
LEFT JOIN
    (
        SELECT
            USERID,
            CMPNYNAM,
            COUNT(*) BatchCount
        FROM
            Dynamics..SY00800
        GROUP BY
            USERID,
            CMPNYNAM
    ) batch
    ON Activity.USERID = batch.USERID
    and Activity.CMPNYNAM = batch.CMPNYNAM
ORDER BY 
    Activity.LOGINDAT,
    Activity.LOGINTIM

Copy Live to Test company - version 4

I've updated my script since my original post back in 2011 that is used to refresh a GP test company with live data.

This tried and tested script will perform the following:

• Back up the current test database (optional; on by default)
• Back up the live database (optional; on by default)
• Restore the live backup (or, optionally, any other SQL backup)
• Run the CreateTestCompany (Microsoft) script
• Set the output settings to print to screen instead of a printer (optional; on by default)
• Change the Recovery Mode to Simple, and shrink the log file

DECLARE
        @SourceDatabase varchar(10),
        @TESTDatabase varchar(10),
        @DatabaseFolder varchar(1000),
        @LogFolder varchar(1000),
        @BackupFolder varchar(1000),
        @BackupFilename varchar(100),
        @TESTBackupFilename varchar(100),
        @RestoreFile varchar(100),
        @RestoreToDataFolder varchar(100),
        @RestoreToLogFolder varchar(100),
        @UseRestoreFile smallint,
        @UseRestoreToFolders smallint,
        @CompressionAllowed smallint,
        @Compress smallint,
        @BackupTESTFirst smallint,
        @LogicalFilenameDB varchar(100),
        @LogicalFilenameLog varchar(100),
        @SetPrintToScreen smallint,
        @SQL varchar(max)
-----------------------------------------------------------------------
-- Set up all the information here for the live and test database names
-----------------------------------------------------------------------

SET        @SourceDatabase =        'TWO'
USE                                [TWO]    -- enter the Source Database between the braces
SET        @TESTDatabase =            'TEST'
SET        @BackupFolder =            'F:\DB Backups\' --end with a backslash (\)
--                                        Folder where you want to save the backup file
SET        @Compress =                1    --  0 = Do not compress backups; 1 = Compress backups
--                                        2008 and forward; SQL Express and some Standard editions do not allow compression
SET        @BackupTESTFirst =        1     --    0 = No; 1 = Yes
--                                        Backup the TEST database before restoring
--                                        You should do this the first time you restore per day
SET        @UseRestoreFile =        0    --    0 = Create a backup and restore it
--                                        1 = Use the below filename to restore from instead of creating new backup
--                                        Use this when you just want to reload a backup you already created
SET        @RestoreFile =            'TWO_20130403.bak'
--                                        File to restore if you have already created a backup of the live company
SET        @UseRestoreToFolders =    1    --    0 = Restore to the same folder as the live database; 
--                                        1 = Restore to the folders specified below
SET        @RestoreToDataFolder =    'G:\SQL Data TEST\' --end with a backslash (\)
--                                        Folder where you want to restore the .mdf (Data) file TO
SET        @RestoreToLogFolder =    'H:\SQL Log TEST\' --end with a backslash (\)
--                                        Folder where you want to restore the .ldf (Log) file TO
SET        @SetPrintToScreen =        1  --    0 = No; 1 = Yes
--                                         Change the posting output to the screen instead of the printer
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
SELECT @CompressionAllowed =    CONVERT(smallint,ISNULL((SELECT value FROM sys.configurations WHERE name = 'backup compression default'),-1))
SELECT @LogicalFilenameDB =        (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 1
SELECT @LogicalFilenameLog =    (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 0

IF @UseRestoreToFolders = 1
    BEGIN
        SET @DatabaseFolder = @RestoreToDataFolder
        SET @LogFolder = @RestoreToLogFolder
        print 'Using specified data folder ' + @DatabaseFolder + ' and log folder ' + @LogFolder
    END
ELSE
    BEGIN
        SELECT TOP 1 @DatabaseFolder =    left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 1
        SELECT TOP 1 @LogFolder =        left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 0
        print 'Using live database''s data folder ' + @DatabaseFolder + ' and log folder ' + @LogFolder
    END

print 'Compression: ' + CASE WHEN @Compress = 1 AND @CompressionAllowed = -1 THEN 'NOT ALLOWED' WHEN @Compress = 1 THEN 'ON' ELSE 'OFF' END
print @LogicalFilenameDB
print @LogicalFilenameLog
print @DatabaseFolder

SET    @SQL = ''

IF @UseRestoreFile = 0
    BEGIN
        SET    @BackupFilename = @BackupFolder + @SourceDatabase +
            '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
            '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
            '_for_' + @TESTDatabase + '.bak'
        END
    ELSE
        BEGIN
            SET @BackupFilename = @BackupFolder + @RestoreFile
        END

print '.'
print 'Backup Filename: ' + @BackupFilename
print '.'

SET @TESTBackupFilename = @BackupFolder + @TESTDatabase +
    '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
    '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
    '_pre_LIVE_restore.bak'

-- check to see if the TEST database is in use first
IF EXISTS(SELECT spid,loginame=rtrim(loginame),hostname,program_name,login_time,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
        WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
        = @TESTDatabase)
    BEGIN
        PRINT 'The database is in use'
        SELECT spid,loginame=rtrim(loginame),hostname,program_name,login_time,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
            WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
            = @TESTDatabase
    END
ELSE
    BEGIN
        IF @UseRestoreFile = 0
        BEGIN

        -- Back up the LIVE database
            SET @SQL = '' +
                'PRINT ''Backing up Source database (' + @SourceDatabase + ')''; ' +
                'BACKUP DATABASE [' + @SourceDatabase + '] TO DISK ' +
                '= N''' + @BackupFilename + ''' ' + 
                'WITH COPY_ONLY, NOFORMAT, NOINIT, NAME ' +
                '= N''' + @SourceDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
                IF @Compress = 1 AND @CompressionAllowed > -1
                    SET @SQL = @SQL + ', COMPRESSION'

            EXEC (@SQL)
        END

        -- Back up the TEST database
        IF @BackupTESTFirst = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Backing up Destination database (' + @TESTDatabase + ')''; ' +
                    'BACKUP DATABASE [' + @TESTDatabase + '] TO DISK ' +
                    '= N''' + @TESTBackupFilename + ''' ' + 
                    'WITH COPY_ONLY, NOFORMAT, NOINIT, NAME ' +
                    '= N''' + @TESTDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
                IF @Compress = 1 AND @CompressionAllowed > -1
                    SET @SQL = @SQL + ', COMPRESSION'
                
                EXEC (@SQL)
            END

        -- Restore to the TEST database
        SET @SQL = '' +
            'PRINT ''Restoring to Destination database (' + @TESTDatabase + ')''; ' +
            'RESTORE DATABASE [' + @TESTDatabase + '] FROM DISK ' +
            '= N''' + @BackupFilename + ''' ' +
            'WITH FILE = 1, ' +
            'MOVE N''' + @LogicalFilenameDB + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Dat.mdf'', ' +
            'MOVE N''' + @LogicalFilenameLog + ''' TO ' +
            'N''' + @LogFolder + 'GPS' + @TESTDatabase + 'Log.ldf'', ' +
            'NOUNLOAD, REPLACE, STATS = 10'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Setting Recovery Model to Simple''; ' +
            'ALTER DATABASE [' + @TESTDatabase + '] SET RECOVERY SIMPLE WITH NO_WAIT'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Shrinking Log File'' ' +
            'USE [' + @TESTDatabase + '] ' +
            'DBCC SHRINKFILE (N''' + @LogicalFilenameLog + ''' , 0, TRUNCATEONLY)'
        EXEC (@SQL)

        IF @SetPrintToScreen = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Setting output to print to screen instead of printer'' ' +
                    'USE [' + @TESTDatabase + '] ' +
                    'UPDATE SY02200 SET PRTOSCNT = 1, PRTOPRNT = 0'
                EXEC (@SQL)
            END

        -- Run the CreateTestCompany script from MS
        SET @SQL = '' +
            'PRINT ''Running CreateTestCompany script on ' + @TESTDatabase + '''; ' +
            'USE [' + @TESTDatabase + '] ' +
            'if not exists(select 1 from tempdb.dbo.sysobjects where name = ''##updatedTables'') ' +
            ' create table [##updatedTables] ([tableName] char(100)) ' +
            'truncate table ##updatedTables ' +
            'declare @cStatement varchar(255) ' +
            'declare G_cursor CURSOR for ' +
            'select ' +
            'case ' +
            'when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'') ' +
            ' then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) ' +
            'else ' +
            '''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' ' +
            'end ' +
            'from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c ' +
            'where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'', ''COMPANYCODE_I'') ' +
            'and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = ''BASE TABLE''; ' +
            'set nocount on; ' +
            'OPEN G_cursor; ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'WHILE (@@FETCH_STATUS <> -1) ' +
            'begin ' +
            'insert ##updatedTables select ' +
            'substring(@cStatement,8,patindex(''%set%'',@cStatement)-9) ' +
            'Exec (@cStatement) ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'end ' +
            'DEALLOCATE G_cursor ' +
            'select [tableName] as ''Tables that were Updated'' from ##updatedTables '
        EXEC (@SQL)
            PRINT 'Don''t forget to run GP SQL Maintenance on the ' + @TESTDatabase + ' database.'
    END

Version 4 changes:

• Specify the restore folder for the data file (.mdf)
• Specify the restore folder for the log file (.ldf)

Version 3 changes:

• Backup compression (optional; on by default)
• Uses the "Copy Only" switch - so it does not mark the database as backed up - useful when using certain other backup software as the primary backup

Version 2 changes:

• Messages window shows the auto-generated backup filename of the LIVE database for use in the optional restore feature below
• Restores a backup you already created (optional)
• Changes the posting output of the TEST company in GP to Screen instead of Printer (optional)
• Changes the Recovery Model to Simple and shrinks the log file of the TEST database

Version 1

• Backs up the TEST database (optional)
• Backs up the LIVE database and restore to the TEST database
• Runs CreateTestCompany script from PartnerSource against the TEST database

How to See the Current Status of a Dynamics GP Upgrade or Service Pack

UPDATE: See updated version here.

I've referenced before that we have a customer with over 80 GP companies; they are the mother of many script inventions for me as I have no desire to manually do anything for 80 databases. Well, now they are at 90.

Needless to say, one of the many challenges with such a large number of companies is keeping track of where the database processing is during an upgrade or service pack. It would also be nice to know how long the thing is going to take.

Here is a handy little script I created to help keep track of this:

Edit 8/20/2014: Fixed a couple letter cases to work properly for Binary SQL sorting
Edit 10/15/2014: See updated script here

DECLARE @NewVersion int
DECLARE @NewBuild int

SET @NewVersion = 11    -- Change this to the major version number you are upgrading TO
SET @NewBuild = 1752    -- Change this to the build number you are upgrading TO

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END

SELECT * 
INTO #Completed_List
FROM (
    SELECT
        Completed_Company = RTRIM(db_name),
        Started_At = (SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0),
        Ended_At = CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN GETDATE() ELSE MAX(stop_time) END,
        Run_Time = 
            CASE WHEN (MIN(db_verOldMajor) <> @NewVersion OR MIN(db_verOldBuild) <> @NewBuild OR MIN(db_verOldMajor) <> MIN(db_verMajor) OR MIN(db_verOldBuild) <> MIN(db_verBuild)) AND MAX(db_status) <> 0 THEN
                GETDATE()-(SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            ELSE
                MAX(stop_time)-(SELECT start_time FROM DB_Upgrade WHERE db_name = DBU.db_name and PRODID = 0)
            END,
        Upgrading = CASE WHEN MAX(db_status) <> 0 THEN 1 ELSE 0 END
    FROM
        DB_Upgrade DBU
    WHERE
        EXISTS(
            select *
            from DB_Upgrade where
            db_verMajor = @NewVersion
            and db_verBuild = @NewBuild 
            AND PRODID =  0
            AND db_name <> 'DYNAMICS'
            and db_name = DBU.db_name
        )
    GROUP BY db_name
) a

SELECT 
    Currently_Upgrading = ISNULL((select TOP 1 db_name from DB_Upgrade where ((db_verMajor = @NewVersion AND db_verBuild = @NewBuild) AND (db_verOldMajor <> db_verMajor OR db_verOldBuild <> db_verBuild)) and PRODID = 0 and db_name <> 'DYNAMICS' order by start_time desc),
                                (SELECT TOP 1 db_name FROM DB_Upgrade WHERE db_status <> 0)),
    Not_Upgraded = (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS'),
    Upgraded = (select COUNT(*) from DB_Upgrade WHERE (db_verMajor = @NewVersion AND db_verBuild = @NewBuild AND db_verOldMajor = db_verMajor AND db_verOldBuild = db_verBuild)  and PRODID = 0 and db_name <> 'DYNAMICS'),
    Average_Time_Per_DB = 
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0))) + 's '
    ,
    Elapsed_Time =
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(SUM(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List))) + 's '
    ,
    Estimated_Remaining_Time = 
        CONVERT(varchar(4),DATEPART(HOUR,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'h ' +
        CONVERT(varchar(4),DATEPART(MINUTE,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 'm ' +
        CONVERT(varchar(4),DATEPART(SECOND,(SELECT CAST(AVG(CAST(Run_Time as FLOAT)) * (select COUNT(*) from DB_Upgrade WHERE (db_verMajor <> @NewVersion OR db_verBuild <> @NewBuild) AND PRODID = 0 and db_name <> 'DYNAMICS') as DATETIME) FROM #Completed_List WHERE Upgrading = 0)
                        +
                        (
                            (SELECT CAST(AVG(CAST(Run_Time as FLOAT)) as DATETIME) FROM #Completed_List WHERE Upgrading = 0) -
                            (SELECT Run_Time FROM #Completed_List WHERE Upgrading = 1)
                        ))) + 's '
                                
SELECT
        Completed_Company = CASE WHEN Upgrading = 1 THEN Completed_Company + ' - Upgrading' ELSE Completed_Company END,
        Started_At,
        Ended_At,
        Run_Time = 
            CONVERT(varchar(4),DATEPART(HOUR,(Run_Time))) + 'h ' +
            CONVERT(varchar(4),DATEPART(MINUTE,(Run_Time))) + 'm ' +
            CONVERT(varchar(4),DATEPART(SECOND,(Run_Time))) + 's '
FROM
    #Completed_List
ORDER BY 
    Upgrading DESC, 
    Ended_At DESC

IF object_id('tempdb..#Completed_List') IS NOT NULL
BEGIN
   DROP TABLE #Completed_List
END

'sa', DYNSA, and POWERUSER in Dynamics GP

Updated 4/2/2015

I sometimes get questions about whether the 'sa' user is required for Microsoft Dynamics GP, what an alternative for 'sa' is, and what the POWERUSER Security Role really does. Well, I'm not a Dynamics GP developer at Microsoft, but here is what I know from what I've seen. Keep in mind this is not a How-To for setting up GP security.

Before going into the differences of each type of user or role, let me explain a little about GP Security. 

During the initial installation and each subsequent company database creation, a database role called DYNGRP is created in each database and assigned permissions to most objects in each database (Tables, Views, and Stored Procedures). This one database role is the basis for all SQL database access for GP users - with the exception of the 'sa' user, but more on that later.

When you create a new user in GP, the following key actions take place, not necessarily in this order and not limited to this list:

• A SQL login is created
• A GP user is created in DYNAMICS..SY01400
• The user is assigned to the DYNGRP database role in the DYNAMICS database
• The user is assigned to the DYNGRP database role in each respective company given to it in the User Access window (after the user is created)
• The user's password is encrypted and saved in SQL

Because all GP users are part of the DYNGRP role, any GP user could log into SQL through the back-end and gain access to pretty much all the data in the GP databases assigned to them - if it weren't for that last line item in the list. Because the passwords are encrypted, users are limited to accessing SQL data through GP.

Now on to the 'sa' and DYNSA users, internal GP Security, the POWERUSER Security Role, and Alternate/Modified Forms and Reports settings.

sa

This is the big cheese of both SQL and GP. There is no greater user than this and there is no GP data it cannot touch. "There is the GP system password," the semi-experienced might say. Well the 'sa' user does not need to be logged in to GP to get to the SQL tables protected by the GP system password.

The ‘sa’ user is also the only GP user who has direct access to the databases from SQL and is the only SQL user that is created outside GP that can log into GP. A special static ‘sa’ user is built into GP, and it cannot be deleted nor have its password changed from inside GP. The 'sa' user is the only GP user who can perform all GP maintenance and administrative tasks inside GP* as well as all maintenance and administrative tasks in SQL. By default, 'sa' has the POWERUSER Role in all companies.

DYNSA

All GP SQL databases are owned by the DYNSA user. This is what allows the DYNSA user to perform most administrative and maintenance tasks without requiring the ‘sa’ user. 

EDIT: This also allows DYNSA to back up company databases inside GP (but cannot restore them - only 'sa' can restore databases). GP adds DYNSA to the SecrityAdmin and dbCreator system roles on installation, so this user can administer GP users but still does not allow creating new GP companies despite the dbCreator role.

System Password

There are certain system-wide administrative tasks that are protected by GP's System Password, not to be confused with the 'sa' password. The System Password protects areas such as GP Security, Multicurrency Access, and other tasks not related to any particular company. You can log in as 'sa' or DYNSA, but if you do not have the System Password you cannot perform those tasks inside GP. Keep in mind that users do not have to be 'sa' or DYNSA to perform some of those tasks as long as they have the System Password.

Internal GP Security

Dynamics GP's security is what keeps regular GP users from having access to all objects in all GP databases given through the DYNGRP SQL database group.

What I describe here relates to Dynamics GP version 10 and above. Version 9 and below security was completely different.

GP Security Operations

Operations are the individual windows and reports to which security access can be given. For example, the Payables Transaction Entry window and Financial Detail Trial Balance Report are Operations.

Operations are defined by the available windows ("forms," in GP terminology) and reports in a given product dictionary. This is not something you will set up as a GP administrator.

GP Security Tasks

Tasks are small groups of Operations that relate to each other. For example, the ADMIN_PURCH_001 task gives access to several windows and reports relating to setting up Purchasing.

GP Security Roles

Roles are groups of Tasks that define a job role. For example, the AP Clerk role allows a user to enter Payables Transactions, maintain Vendors, look up GL entries, and print Purchasing reports.

There are plenty of pre-defined Tasks and Roles available out of the box. I suggest that instead of editing the defaults you create new Tasks and Roles based on the default ones. This will help you or your Microsoft Partner identify which ones are non-standard if you run into any issues related to security.

When giving access to users, you will be specifying which Role(s) they will have and for which company. A user can have multiple Roles and can have different Roles in each GP company.

POWERUSER Role

Unlike the AP Clerk Role where its access is explicitly defined, the POWERUSER Role implicitely has access to everying the DYNGRP database role has access to in a specified GP company database. The 'sa' and DYNSA users are automatically part of the POWERUSER Role. Giving a regular user all other Security Roles is not the equivalent of the POWERUSER Role.

Alternate/Modified Forms and Reports Settings

In Dynamics GP version 9 and below, setting someone up to use a modified report was very granular and did not allow for very easy administration. In version 10 and later, it is like setting up a group of on/off switches: for each alternate or modified option available on the workstation you are using to set this up, you will specify whether to use the default or the alternate/modified alternative. Yes, since you can technically be using different report dictionaries and different modules per workstation, the Alternate/Modified Forms and Reports settings are workstation-specific; most of the time you'll want to utilize the same modules and a shared location for modified reports, but that's not always the case. Alternate/Modified Forms and Report settings can be specified per user per company.

*Adding Additional System Security

As noted by Devo (thanks for that find!), additional security can be added to users to perform certain administrative tasks, such as adding users and companies. See pages 37-40 of the GP Security Planning guide found here.

EDIT: To give users access to be able to create new users or update passwords, assign the SQL login to the SecurityAdmin server role. To give users access to be able to add new companies in GP Utilities, assign the SQL login to the sysadmin server role.

Conclusion

I hope this helps answer at least a few questions regarding how Microsoft Dynamics GP and Microsoft SQL Server work together to provide security for Dynamics GP as well as clarifies how security is implemented inside GP.

If you found this helpful, please leave a comment.

Dynamics GP Security Reports for SQL Reporting Services

User Roles User Tasks User Operations

It is not very easy to find out the current status of everyone’s security in GP. Sure, there are a some standard reports inside GP, but they are not the most user-friendly way to quickly track down who has what permissions to GP’s windows and reports.
That is exactly why we created GP Security Reports for SQL Reporting Services.
There are currently 9 reports in this package to give you at-a-glance, drill-down views of your Dynamics GP security:

1. Role Tasks
2. Task Operations
3. Company Roles – Per User
4. Company Roles – All Users
5. User Roles – Per Company
6. User Roles – All Companies
7. User Tasks – Per Company
8. User Tasks – All Companies
9. User Operations – Per Company

If you would like to find how to purchase these security reports, e-mail info@combussol.com or call 561-392-8135.

GP Security Upgrades

We still have customers upgrading to Dynamics GP 10 or 2010 from version 9 and, even scarier, from version 8. One of the biggest changes starting in version 10 is the security model. The absolute management nightmare of previous versions was changed to something resembling an Active Directory-ish "resource - task group - role group - user" model. This was Microsoft's best addition to GP 10.

When upgrading to version 10 or 2010 from version 9, GP Utilities asks if it should upgrade security. No. NO! Resist the temptation to take the fast track! You will only bring over the junk you are running away from. Start fresh, clean, and clutter-free.

Starting fresh with security also gives the added value of revisiting security requirements. More than a few of our customers did not want to mess with the over-complicated security model of old and just left everyone with access to everything. Not the Sarbanes-Oxley approach, but they had accounting to attend to. When they did take the time to implement security their users would start out with the default permissions assigned by their User Class, and that would be the last time their security ever matched the defaults. A report "I need to run for So-And-So" here, a window "I have to enter in transactions while So-And-So is on vacation" there - all undocumented, of course, and no one would have the guts to reset them back to the default User Class because that would be a phone call from an unhappy user who "can't do anything" just waiting to happen.

Version 10/2010's Security Roles have given nearly all of our customers a reason to implement real security. It's a whole lot easier to figure that their AP Clerk gets the AP Clerk role and that additional permissions are given through the additional role created just for those additions. As far as whether or not to edit the default roles, my suggestion is to leave the default roles alone and either create new ones based on the defaults or create new ones with just the additional permissions. Someone's on vacation? You can give their role to the their underpaid grunt and take it back upon return. One checkbox.

One thing to keep in mind is that you cannot deny access - only allow access. Maybe you have several people performing the same tasks except for one of them who shouldn't see a handful of those windows or reports. You would create one role with all the common tasks and another with the exceptions.

My unique contribution to this subject is this security worksheet to help get started with version 10's security before the upgrade. This is a .zip file with two Excel spreadsheets. One is mainly designed to show the default Tasks contained within each default Role, and the other is a worksheet to assign Users to Roles. Both are derived from a set of SQL Reports we created in-house to give a better visualization than the canned GP security reports. My next post explains more about those reports.

Download the security worksheets here

Copy Live to Test Company

Note: see updated version here

One of those "busy work" tasks for a Dynamics GP consultant is when we need to create a test company and copy a live database into the test company. Well, I'm not about to automate the creating of the company in GP Utilities, but I can certainly help with the part of backing up and restoring the live database into the test company and running the Microsoft CreateTestCompany.sql script afterwards.
This tried and tested script will perform the following:

• (Optional) Back up the live company database
• (Optional) Back up the test company database
• Restore the live company backup (or, alternately, any specified backup file) over the test company database
• Run Microsoft's CreateTestCompany.sql script against the test company database
• (Optional) Change the posting output options to Screen instead of Printer or File

I find it extremely useful when I need to restore it several times to get a procedure down right.

DECLARE @SourceDatabase varchar(10),
        @TESTDatabase varchar(10),
        @DatabaseFolder varchar(1000),
        @BackupFolder varchar(1000),
        @BackupFilename varchar(100),
        @UseRestoreFile tinyint,
        @RestoreFile varchar(100),
        @TESTBackupFilename varchar(100),
        @BackupTESTFirst tinyint,
        @LogicalFilenameDB varchar(100),
        @LogicalFilenameLog varchar(100),
        @SetPrintToScreen tinyint,
        @SQL varchar(max)
-----------------------------------------------------------------------
-- Set up all the information here for the live and test database names
-----------------------------------------------------------------------

SET        @SourceDatabase =    'TWO'
USE                             [TWO]    -- enter the Source Database between the braces
SET        @TESTDatabase =      'TEST'
SET        @BackupFolder =      'D:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\' --end with a backslash (\)
--                               Folder where you want to save the backup file
SET        @UseRestoreFile =     0    --    0 = Create a backup and restore it
--                                    --    1 = Use the below filename to restore from instead of creating new backup
--                                    Use this when you just want to reload a backup you already created
SET        @RestoreFile =       'TWO_20110111.bak'
SET        @BackupTESTFirst =    1     --    0 = No; 1 = Yes
--                                    Backup the TEST database before restoring
--                                    You should do this the first time you restore per day
SET        @SetPrintToScreen =   1    --    0 = No; 1 = Yes
--                                    Change the posting output to the screen instead of the printer
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------
SELECT @LogicalFilenameDB =      (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 1
SELECT @LogicalFilenameLog =     (rtrim(name)) FROM dbo.sysfiles WHERE groupid = 0
SELECT @DatabaseFolder =         left(filename,len(filename)-charindex('\',reverse(filename))+1) FROM dbo.sysfiles WHERE groupid = 1
print @LogicalFilenameDB
print @LogicalFilenameLog
print @DatabaseFolder

SET    @SQL = ''

IF @UseRestoreFile = 0
    BEGIN
        SET    @BackupFilename = @BackupFolder + @SourceDatabase +
            '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
            '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
            '_for_' + @TESTDatabase + '.bak'
        END
    ELSE
        BEGIN
            SET @BackupFilename = @BackupFolder + @RestoreFile
        END

print '.'
print 'Backup Filename: ' + @BackupFilename
print '.'

SET @TESTBackupFilename = @BackupFolder + @TESTDatabase +
    '_' + convert(varchar(4),year(GETDATE())) + right('00' + convert(varchar(2),month(GETDATE())),2) + right('00' + convert(varchar(2),day(GETDATE())),2) +
    '_' + replace(CONVERT(VARCHAR(8),GETDATE(),108),':','') +
    '_pre_LIVE_restore.bak'

-- check to see if the TEST database is in use first
IF EXISTS(SELECT loginame=rtrim(loginame),hostname,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
        WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
        = @TESTDatabase)
    BEGIN
        PRINT 'The database is in use'
        SELECT loginame=rtrim(loginame),hostname,dbname = (CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END) FROM master.dbo.sysprocesses
            WHERE CASE WHEN dbid = 0 THEN NULL WHEN dbid <> 0 THEN db_name(dbid) END
            = @TESTDatabase
    END
ELSE
    BEGIN
        IF @UseRestoreFile = 0
        BEGIN

        -- Back up the LIVE database
            SET @SQL = '' +
                'PRINT ''Backing up Source database (' + @SourceDatabase + ')''; ' +
                'BACKUP DATABASE [' + @SourceDatabase + '] TO DISK ' +
                '= N''' + @BackupFilename + ''' ' +
                'WITH NOFORMAT, NOINIT, NAME ' +
                '= N''' + @SourceDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
            EXEC (@SQL)
        END

        -- Back up the TEST database
        IF @BackupTESTFirst = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Backing up Destination database (' + @TESTDatabase + ')''; ' +
                    'BACKUP DATABASE [' + @TESTDatabase + '] TO DISK ' +
                    '= N''' + @TESTBackupFilename + ''' ' +
                    'WITH NOFORMAT, NOINIT, NAME ' +
                    '= N''' + @TESTDatabase + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
                EXEC (@SQL)
            END

        -- Restore to the TEST database
        SET @SQL = '' +
            'PRINT ''Restoring to Destination database (' + @TESTDatabase + ')''; ' +
            'RESTORE DATABASE [' + @TESTDatabase + '] FROM DISK ' +
            '= N''' + @BackupFilename + ''' ' +
            'WITH FILE = 1, ' +
            'MOVE N''' + @LogicalFilenameDB + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Dat.mdf'', ' +
            'MOVE N''' + @LogicalFilenameLog + ''' TO ' +
            'N''' + @DatabaseFolder + 'GPS' + @TESTDatabase + 'Log.ldf'', ' +
            'NOUNLOAD, REPLACE, STATS = 10'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Setting Recovery Model to Simple''; ' +
            'ALTER DATABASE [' + @TESTDatabase + '] SET RECOVERY SIMPLE WITH NO_WAIT'
        EXEC (@SQL)

        SET @SQL = '' +
            'PRINT ''Shrinking Log File'' ' +
            'USE [' + @TESTDatabase + '] ' +
            'DBCC SHRINKFILE (N''' + @LogicalFilenameLog + ''' , 0, TRUNCATEONLY)'
        EXEC (@SQL)

        IF @SetPrintToScreen = 1
            BEGIN
                SET @SQL = '' +
                    'PRINT ''Setting output to print to screen instead of printer'' ' +
                    'USE [' + @TESTDatabase + '] ' +
                    'UPDATE SY02200 SET PRTOSCNT = 1, PRTOPRNT = 0'
                EXEC (@SQL)
            END

        -- Run the CreateTestCompany script from MS
        SET @SQL = '' +
            'PRINT ''Running CreateTestCompany script on ' + @TESTDatabase + '''; ' +
            'USE [' + @TESTDatabase + '] ' +
            'if not exists(select 1 from tempdb.dbo.sysobjects where name = ''##updatedTables'') ' +
            ' create table [##updatedTables] ([tableName] char(100)) ' +
            'truncate table ##updatedTables ' +
            'declare @cStatement varchar(255) ' +
            'declare G_cursor CURSOR for ' +
            'select ' +
            'case ' +
            'when UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'') ' +
            ' then ''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''+ cast(b.CMPANYID as char(3)) ' +
            'else ' +
            '''update ''+a.TABLE_NAME+'' set ''+a.COLUMN_NAME+'' = ''''''+ db_name()+'''''''' ' +
            'end ' +
            'from INFORMATION_SCHEMA.COLUMNS a, DYNAMICS.dbo.SY01500 b, INFORMATION_SCHEMA.TABLES c ' +
            'where UPPER(a.COLUMN_NAME) in (''COMPANYID'',''CMPANYID'',''INTERID'',''DB_NAME'',''DBNAME'', ''COMPANYCODE_I'') ' +
            'and b.INTERID = db_name() and a.TABLE_NAME = c.TABLE_NAME and c.TABLE_CATALOG = db_name() and c.TABLE_TYPE = ''BASE TABLE''; ' +
            'set nocount on; ' +
            'OPEN G_cursor; ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'WHILE (@@FETCH_STATUS <> -1) ' +
            'begin ' +
            'insert ##updatedTables select ' +
            'substring(@cStatement,8,patindex(''%set%'',@cStatement)-9) ' +
            'Exec (@cStatement) ' +
            'FETCH NEXT FROM G_cursor INTO @cStatement ' +
            'end ' +
            'DEALLOCATE G_cursor ' +
            'select [tableName] as ''Tables that were Updated'' from ##updatedTables '
        EXEC (@SQL)
            PRINT 'Don''t forget to run GP SQL Maintenance on the ' + @TESTDatabase + ' database.'
    END