PRB: "Error 229: Execute Permission Denied on Object 'xp_availablemedia' Owner 'dbo'" (323249)

SYMPTOMS

In SQL Server Enterprise Manager, if you follow these steps

Right-click a database, and then click All Tasks - Backup Database.
Click Add for the destination, and then click the ellipsis button (...) to select a new backup destination.

you receive the following error message:

Error 229: Execute permission denied on object "xp_availablemedia" owner "dbo"

CAUSE

This problem occurs under the following conditions:

Your SQL Server logon user profile is a member of the public or the db_owner role (or both).
You cannot browse the local directory tree, and you cannot back up or restore a database without typing the actual path and the backup file or device name.

This problem can also occur when you try to back up a SQL Server 7.0 database by using SQL Server Enterprise Manager from SQL Server 2000.

Only members of the SQL Server system administrators role (sysadmin) can view the directory structure and select local backup files. This behavior prevents you from accidentally overwriting backup files.

WORKAROUND

To work around this behavior, assign the user profile to the sysadmin role. By doing so, the user profile has unlimited access to all SQL Server features.

REFERENCES

For more information about the sysadmin role, view the "Adding a Member to a Predefined Role" topic in SQL Server Books Online.