Upg to 7.2 Error on final script

[DannyC]

I've upgraded a 6.4.3 database (you know who!) to 7.2, using the tips suggested in viewtopic.php?f=26&t=991

I've done it a few times, and each time, the upgrade scripts fail on the final script "Grant permissions".
I can't find that specific script in the Access database or anywhere else.

Can you post the SQL script here so I can review it & see what's going on?

Thanks

[Scott.Pearce]

Can you show me the schema for the HR_Staff table in 6.4.3? I don't have a 6.4.3 database handy...

[DannyC]

But wouldn't the HR_Staff table have gone through any upgrade scripts and therefore would be what any 7.2 version is?

After upgrading, the HR_Staff table is this:

Code: Select all

CREATE TABLE [dbo].[HR_Staff](
   [StaffID] [char](20) NOT NULL,
   [LastSavedDateTime] [datetime] NULL,
   [Title] [varchar](10) NULL,
   [FName] [varchar](50) NULL,
   [SName] [varchar](50) NULL,
   [Position1] [varchar](50) NULL,
   [Position2] [varchar](50) NULL,
   [Password] [varchar](255) NULL,
   [IsActive] [bit] NULL,
   [EmailAddress] [varchar](255) NULL,
   [EmailDisplayName] [varchar](255) NULL,
   [SMTPUsername] [varchar](255) NULL,
   [SMTPPassword] [varchar](255) NULL,
   [Username] [varchar](50) NOT NULL,
   [MustChangePasswordNextLogin] [bit] NULL,
   [PasswordLastChangedDateTime] [datetime] NULL,
   [SQLLogin] [varchar](255) NULL,
   [SQLPassword] [varchar](255) NULL,
   [ReportSQLLogin] [varchar](255) NULL,
   [ReportSQLPassword] [varchar](255) NULL,
   [Picture] [varbinary](max) NULL,
   [HR_Departments_RecID] [char](36) NULL,
   [Extension] [varchar](20) NULL,
   [Mobile] [varchar](20) NULL,
   [UsePrepaidLabourPacks] [bit] NULL,
   [AuthenticationModes] [tinyint] NOT NULL,
   [WindowsDomainAccount] [varchar](50) NULL,
   [SMTPPasswordLastChangedDateTime] [datetime] NULL,
   [SY_Menu_RecID] [char](36) NULL,
   [SY_PrintGroup_RecID] [char](36) NOT NULL,
   [SQLLoginLastSavedDateTime] [datetime] NOT NULL,
   [ReportsLoginLastSavedDateTime] [datetime] NOT NULL,
   [IsEnabled] [bit] NOT NULL)


[Scott.Pearce]

I need to see the table schema as of 6.4.3.

[DannyC]

Here's the schema from 6.4.3

Code: Select all

CREATE TABLE [dbo].[HR_Staff] (
   [StaffID] [char] (20) NOT NULL ,
   [LastSavedDateTime] [datetime] NULL ,
   [Title] [varchar] (10)  NULL ,
   [FName] [varchar] (50)  NULL ,
   [SName] [varchar] (50)  NULL ,
   [Position1] [varchar] (50) NULL ,
   [Position2] [varchar] (50)  NULL ,
   [Password] [varchar] (50)  NULL ,
   [ActiveLogIn] [bit] NULL
) ON [PRIMARY]
GO

[Scott.Pearce]

Hmmmm, it wasn't what I suspected then.

What happens if you run this on the (almost) upgraded database:

Code: Select all

EXEC usp_Jiwa_Grant_Application_Permissions 'JiwaUser'

and then this:

Code: Select all

EXEC usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'

Do they both execute OK, or do you get an error? If you get an error, what is it?

[DannyC]

Ah, OK so I see the issue...

the Grant to JiwaUser works OK, but the Grant to JiwaReports comes up with an error.

Code: Select all

GRANT SELECT ON [_Group] TO JiwaReports
Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'JiwaReports', because it does not exist or you do not have permission.


Looking in SQL Management Studio, I can see that there isn't a JiwaReports user. Easy to fix. I can chuck in a script to add the JiwaReports user.

[Scott.Pearce]

Ah yes, "JiwaReports" was not a thing back in v6.4.3. You will also have to hook up the newly created "JiwaReports" user to staff records via Staff Maintenance.

[DannyC]

Ah yes, "JiwaReports" was not a thing back in v6.4.3

Wouldn't there have been an upgrade script somewhere along the line that created the JiwaReports user?
I've done a seacrh through the Access database & can't find anything but it must've somehow become a thing & gotten there via an upgrade script?