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