The execute permission was denied on the object “xp_prop_oledb_provider”

You got “xp_prop_oledb_provider” Error. That is easy.

INTERVIEW PREPARATION IN 30 MINs - Read Following wires for a quick interview preparation.

  1. SQL Server DBA “Interview Questions And Answers”
  2. SQL Azure Interview Questions and Answers Part – 1
  3. SQL Azure Interview Questions and Answers Part – 2
  4. Powershell Interview Questions and Answers

Let me first discuss how to Add linked server. How to add a login in the linked server?

Use following command to do this.

EXEC sp_addlinkedserver
@server = ‘servername’,
@srvproduct = ‘Oracle’,
@provider = ‘OraOLEDB.Oracle’,
@datasrc = ‘datasource’

Add a login using following query

sp_addlinkedsrvlogin @rmtsrvname = ‘job’
, @useself = ‘false’
, @locallogin = ‘LocalLogin’
, @rmtuser = ‘rmtuser’
, @rmtpassword = ‘password’

Now you get the error: The execute permission was denied on the object ‘xp_prop_oledb_provider’

Resolve this as follows:
The login must have the public role in the master database
Execute following command to give to this login execute permission on the extended stored procedure

USE master;
Grant execute on sys.xp_prop_oledb_provider to login;
GO
In case of corruptions: http://mssqlcorruptiontackle.blogspot.com/2010/12/ms-sql-corruption.html
Regards,
Online MSSQL Classes @ http://tuitionaffordable.webstarts.com

About these ads

About tuitionaffordable

Making tuition affordable for all the students on the planet.
This entry was posted in Education, Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s