KB #240035: SQL Error: Execute permission denied on object xp_n_*

Server: Msg 229, Level 14, ...
EXECUTE permission denied on object 'xp_n_decrypt2', database 'master', owner 'dbo'.

• Ensure that the target database is owned by ‘sa’. This is the default, however you can determine the owner by the following query:

     exec sp_helpdb 'northwind'
  

  If the owner is not ‘sa’, change with the following:

     use northwind
     exec sp_changedbowner 'sa'
  

• Enable Cross Database Chainging as follows:

      exec sp_dboption 'northwind', 'db chaining', 'on'
  

Method 2 – Grant Specific Permissions

• Add user from application database to Master database.
• Grant execute permission to required extended stored procedures. Procedures required by Col-E are xp_n_encrypt2 and xp_n_decrypt2.

For example:

    use master
    create user appuser1
    grant execute on xp_n_encrypt2 to appuser1
    grant execute on xp_n_decrypt2 to appuser1
    grant execute on xp_n_decrypt2m to appuser1