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