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

Advertisements

One comment

  1. Thanks for the info, however, I have a team of 20 users that all use the linked server, and I was hoping to avoid having to set them all up and grant specific permissions to them individually (and then maintain that going forward). I have already set up database roles to control who can access what on our server, but I can’t find a way to grant execute on that xp procedure to any of those roles. Is that possible? Or is that yet another hole in microsoft’s security model?

    Thanks,

    Graeme
    (Melbourne, Australia)

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