Jiwa

Separate instance....still okay to run the command:

usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
GO

Luehman wrote:Separate instance....still okay to run the command:

usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
GO

Yep, but beforehand you might want to drop the users and re-create them.

This script will do all you need for those credentials you provided:

Code: Select all

DROP USER JiwaLogin
DROP USER JiwaReports
DROP USER JiwaUser
GO

CREATE USER JiwaLogin FOR LOGIN JiwaLogin
CREATE USER JiwaReports FOR LOGIN JiwaReports
CREATE USER JiwaUser FOR LOGIN JiwaUser
GO

UPDATE HR_Staff
   SET SQLLogin = '6B4A7C2B2E902402268692E8932CDB50',
   SQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
   ReportSQLLogin = '887BFFE23AB10B3C5412655265278A11',
   ReportSQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
   SQLLoginLastSavedDateTime = '2014-05-28 11:20:48.000',
   ReportsLoginLastSavedDateTime = '2014-05-28 11:20:48.000'
WHERE UserName = 'Admin'
GO

-- Set Jiwa Password
UPDATE HR_Staff SET Password = '60A889064BAD474508723B9200184D09',
       PasswordLastChangedDateTime = '2014-05-28 11:20:48.000',
       LastSavedDateTime = '2014-05-28 11:20:48.000',
       SQLLogin = '6B4A7C2B2E902402268692E8932CDB50',
       SQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C',
       ReportSQLLogin = '887BFFE23AB10B3C5412655265278A11',
       ReportSQLPassword = '1C57594FC45BBF79E1CB63D5452D4EE364CB73ED467F7C8C'
WHERE UserName = 'Admin'
GO

usp_Jiwa_Grant_Application_Permissions 'JiwaUser'
GO

usp_Jiwa_Grant_Reporting_Permissions 'JiwaReports'
GO

I run the Query, run out with the following output (didn't paste the whole lot) however still getting the Login Error:

Granting permissions...
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_History] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Main] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Provider] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_ProviderFilters] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_ProviderHeaders] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Schedules] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_TaskFilters] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_TaskHistory] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [AR_Tasks] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProduction] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionCustomFields] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionCustomValues] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionDocuments] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionNotes] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_BatchProductionWorkOrder] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_CustomSetting] TO JiwaUser
GRANT SELECT, INSERT, UPDATE, DELETE ON [BM_CustomSettingValues] TO JiwaUser

Ran the latest script you posted and now get this when running it:

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.
Msg 15138, Level 16, State 1, Line 2
The database principal owns a schema in the database, and cannot be dropped.
Msg 15138, Level 16, State 1, Line 3
The database principal owns a schema in the database, and cannot be dropped.
Msg 15023, Level 16, State 1, Line 2
User, group, or role 'JiwaLogin' already exists in the current database.
Msg 15023, Level 16, State 1, Line 3
User, group, or role 'JiwaReports' already exists in the current database.
Msg 15023, Level 16, State 1, Line 4
User, group, or role 'JiwaUser' already exists in the current database.

Also, still cannot log in...

Use Jiwa 7.2.1 to create a demo database on the same server/instance as the upgraded database. Call this new database "JiwaDemo".

Then run this command on the upgraded database:

Code: Select all

update hr_staff set SQLLogin = (SELECT TOP 1 SQLLogin FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set SQLPassword  = (SELECT TOP 1 SQLPassword FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set ReportSQLLogin  = (SELECT TOP 1 ReportSQLLogin FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set ReportSQLPassword = (SELECT TOP 1 ReportSQLPassword FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set SQLLoginLastSavedDateTime   = (SELECT TOP 1 SQLLoginLastSavedDateTime FROM JiwaDemo.dbo.HR_Staff)
update hr_staff set ReportsLoginLastSavedDateTime   = (SELECT TOP 1 ReportsLoginLastSavedDateTime FROM JiwaDemo.dbo.HR_Staff)


A pain, I know, but we are obliged to take security pretty seriously, especially when company data is involved.

Thanks, understood re security....

Unfortunately still no dice....still getting the same error message when trying to log in....

Sorry, just re-read your post...okay...I''ll create a demo dB etc.

What should I put in these fields?



I assume my SA for the SQL Login field however I do not know the Report SQL one....

I ended up using (for JiwaDemo dB creation):

JiwaLogin / JiwaApplicationLogin123
JiwaReports / JiwaFinancials123

I can log in to the JiwaDemo dB no issues (ran the previous SQL Query against the upgraded dB)....however I still cannot login to in the upgraded dB....see error below:

Was the upgrade run on a different server than the one the database now resides on?

If so try

Code: Select all

EXEC sp_change_users_login 'Auto_Fix', 'JiwaUser'
EXEC sp_change_users_login 'Auto_Fix', 'JiwaLogin'
EXEC sp_change_users_login 'Auto_Fix', 'JiwaReports'