Saturday 6 September 2014

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

2 comments:

  1. Nice post, Peter. How is this different from using the PowerShell command? http://technet.microsoft.com/EN-US/library/jj720241.aspx

    ReplyDelete
    Replies
    1. Hi,
      As far as I know, Grant-AXModelStore is granting the permission to the model database (for the AOS account) only. Given the scenario is to copy both the AX and model database, the permission on AX database needs some editing. I guess we can use the script in the post to update AX database permission, then choose either use the same method in the post or use the powershell for the model database.

      Delete