Copy AX and model database permission (stored proc & view)

Sometimes during development or testing, we might need to create/copy environment. The easiest method is to copy the AX and model database from a source environment, then restore it to the destination environment. If each of the environment (AOS) has different service account, then you might hit permission error during start up of the AOS (if permission haven't been granted yet).

The AOS service account needs certain permission on both of the database, creating it manually will be a lot of work, Instead of doing that, scripting it out would be much easier.

Steps:
#1. Execute the following script from the source environment

set nocount on

Print 'Column Level Privileges to the User:'

select 'grant '+privilege_type+' on '+table_schema+'.'+table_name+' ('+column_name+') to ['+grantee+']'+

case IS_GRANTABLE when 'YES' then ' With GRANT OPTION'

else '' end from INFORMATION_SCHEMA.COLUMN_PRIVILEGES

Print 'Table Level Privileges to the User:'select 'grant '+privilege_type+' on '+table_schema+'.'+table_name+' to ['+grantee+']' +

case IS_GRANTABLE when 'YES' then ' With GRANT OPTION'else ''

end from INFORMATION_SCHEMA.TABLE_PRIVILEGES

Print 'Privileges for Procedures/Functions to the User:'

select 'grant execute on '+c.name+'.'+a.name+' to ['+user_name(b.grantee_principal_id)++']'+

case state when 'W'

 then ' with grant option'else ''

  end from sys.all_objects a, sys.database_permissions b,

  sys.schemas c

  where a.object_id = b.major_id and a.type in ('P','FN') and b.grantee_principal_id <> 0 and b.grantee_principal_id <>2

  and a.schema_id=c.schema_id

Ref: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/758e2485-1def-4549-a4d4-4568b028009d/copy-user-permission

#2. Replace the service account name
Eg. Replace all "AOSService_STD" with "AOSService_DEV"

#3. Execute the result of the script to the corresponding destination database

AX 2012 R3 email report not working

One of the common requirement for report is emailing (Eg. nightly batch run and email the report).

Standard AX does provides this feature, but unfortunately AX 2012 R3 RTM seems to have a bug over here.

Given the Print destination settings below, it looks OK on the first look, but it wasn't. The problem with it is the Cc field is empty.

It uses the SrsReportRunMailer class, emailReport method to send the email.
The mailer.quickSend() method call has all the parameters passed in (including the Cc).

Here's where the problem comes from, the SysMailerNet class, quickSend method. is checking the Cc value using prmisDefault() method.

Sequence:
1. Print destination settings doesn't provides value in Cc field
2. Empty string is passed in as parameter value in quickSend method
3. Due to empty string is passed in, the Cc parameter is no longer default value
4. Validating using the prmisDefault method is incorrect due to Cc is no longer default value (it should just check whether the Cc has value or not - Eg. if(cc){...})

The error occur when it reach the line - ccs.add(cc).
If cc is empty, it error and stop there.

There're two quick solutions here:
1. Enter a value in Cc field
2. Modify the code, use if(cc){...} instead of if(!prmisDefault(cc)){...}