General Use

If you have created a user view or user table you do not need to grant any additional permissions to objects if they are being used in a user form or report within Synergetic.

However, if the user form or report is acessible by Community Portal then additional permissions are required.

If you are creating stored procedures to be run within a Crystal Report you will also need to additional permissions.

New database objects

User objects created for use with Crystal Reports run via Synergetic or SynWeb

Synergetic makes use of database roles that have implied permission to all tables and views in a particular schema.  This means that if you create a table or view, you DO NOT have to grant any permissions to the report database role.  However if you create a stored procedure you MUST grant permission to the report role in order to run it through Synergetic.

Please note that you should grant rights to the database ROLE, not the user.  The user will inherit all permissions of whatever roles they belong to

Non-financial Objects

for example if you have a new stored procedure called dbo.ucrspMyNewProc this will need to have rights assigned to the crystal groups

GRANT EXEC ON dbo.ucrspMyNewProc TO Synergetic_report_finance_ReservedDBRole

Note that even though the report is not a financial one, you must still grant rights to the finance role as Synergetic uses this role when actually running the crystal report.

If your organisation has multiple financial entities you would also need to grant permission to the other financial role(s).

GRANT EXEC ON dbo.ucrspMyNewProc TO Synergetic_report_foundation_ReservedDBRole

Financial Objects

If you have created a stored procedure called finance.ucrspMyFinancialReport you only assign rights to the matching database role

GRANT EXEC ON finance.ucrspMyFinancialReport TO Synergetic_report_finance_ReservedDBRole

That is it...  DO NOT try to grant permission to any other role, eg do not grant access to the dbo role or foundation role.

For new objects created in the single database structure, if you want to have them available for Crystal Reports you will need to grant the appropriate permissions.

In V68 you will need to grant EXECUTE permissions to the DBRole (NOT the zUser).

This way you can add or remove zUsers from the Database and the permissions will be unaffected. Simply add the required zUser to the DBRole (that has all the permissions) and that zUser will "inherit" the permissions.

Use of the finance schema

Note that all reports now run under context of the finance schema Synergetic_report_finance_ReservedDBRole including non-finance. For additional finance schemas (foundation etc) they will run under their equivalent context (ie. Synergetic_report_foundation_ReservedDBRole).

Stored procedures (generally using the 'ucrsp' prefix) will need additional explicit permissions granted if (re)created after v68 upgrade if they are to be used in reports.

New stored procedure objects

Grant EXECUTE for stored procedures. 

GRANT EXECUTE ON {schema}.[object] TO [Synergetic_report_{finance schema}_ReservedDBRole]

For example:

GRANT EXECUTE ON dbo.ucrspMyObjectName to Synergetic_report_finance_ReservedDBRole						-- dbo objects - use the default 'finance' schema
GRANT EXECUTE ON finance.ucrspMyFinanceObjectName to Synergetic_report_finance_ReservedDBRole			-- finance objects - use the 'finance' schema
GRANT EXECUTE ON extrafinance.ucrspMyFinanceObjectName to Synergetic_report_extrafinance_ReservedDBRole	-- additional finance objects - use the appropriate schema

Objects referenced in Crystal Reports run via Community Portal

If any object is required in a report accessible by via Community Portal, explicit permission are required depending on the schema.

If you are referencing any existing database objects in the Community Portal, these may need to have permissions granted similarly to user objects.

GRANT SELECT ON [schema].[object] TO [Synergetic_CommunityPortalReports_{schema}_ReservedDBRole]

ie.

GRANT SELECT ON dbo.[object] TO [Synergetic_CommunityPortalReports_dbo_ReservedDBRole]
GRANT SELECT ON finance.[object] TO [Synergetic_CommunityPortalReports_finance_ReservedDBRole]
GRANT SELECT ON extrafinance.[object] TO [Synergetic_CommunityPortalReports_extrafinance_ReservedDBRole]

The same will apply with granting execute permissions to any stored procedures that are used.

Synonym Database