sys.xp_prop_oledb_provider

Question

Post reply

sys.xp_prop_oledb_provider

k.przewozniak

Ten Centuries

Points: 1133
More actions
February 5, 2009 at 8:23 am

#199675

Hello,
Can you explain me what are the consequences of granting users this permission:
GRANT EXECUTE ON sys.xp_prop_oledb_provider TO
I gave it to users in order to let him see Linked Servers Catalogs. Now I wonder it that is the only change of his permissions or does he get also something "extra" without my knowledge. :w00t:

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pixye.sb SSCommitted Points: 1520 More actions · Answer 1

I have a problem in adding a linked server from sql 2005x64 to oracle.

I have created the linked server

EXEC sp_addlinkedserver

@server = 'job',

@srvproduct = 'Oracle',

@provider = 'OraOLEDB.Oracle',

@datasrc = 'tns'

and added a login

sp_addlinkedsrvlogin @rmtsrvname = 'job'

, @useself = 'false'

, @locallogin = 'opTrasp'

, @rmtuser = 'remUs'

, @rmtpassword = 'pwd'

but I have the error

The EXECUTE permission was denied on the object 'xp_prop_oledb_provider', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

in the test server, running sql 2000 instead of sql 2005) it worked without assigning any further permission to the local login.

Is it necessary? and is it 'dangerous', considering that it is a cluster with many critical databases of different suppliers?

Thanks in advance for your kind replies.

sb

jol1185 SSC Enthusiast Points: 189 More actions · Answer 2

I also needed to know what "xp_prop_oledb_provider" is all about? Can anyone help?

Are we granting another access to the user when we grant them additional execute permission in this procedure? :unsure:

wama316 Valued Member Points: 55 More actions · Answer 3

revisa este link

http://blogs.msdn.com/b/john_daskalakis/archive/2010/02/01/9956271.aspx

por ahi te dejo lo mas importante.

USE master;

Grant execute on sys.xp_prop_oledb_provider to login;

GO

(replace login with the actual login you are using that gets this error)

😀

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 4

In my situation, we had a linked server that was accessed from the reports. The database had the correct permission through a linked server, the account that the reports used had the right permission, but the linked server was not setup to allow the report account through (the linked server). Once that account was added with impersonation to the linked server, the error went away.

Jamie