Knowledge Base

Search Knowledge Base

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

Type: Fix
Summary:
When attempting to access an encrypted column as a non-sysadmin user you may receive the following error:

Server: Msg 229, Level 14, ...
EXECUTE permission denied on object 'xp_n_decrypt2', database 'master', owner 'dbo'.
Additional Information:
Each one of the following methods requires Sysadmin rights.Method 1 – Enable Cross Database Chaining

  • 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

 

Last modified: 1/13/2016

Top