December 28, 2009 at 1:26 pm
Greetings all,
So last week, I was attempting to give our company report builder access to use a linked server to an excel file on the same server as the SQL Server (Yes I came up with a much better solution, but this is the back story to the problem).
I was able to use the linked server, but he was not, and after several attempts to give him access (I admit I have never granted security to a linked server object before and was going by trial and error). At one point I assigned him to the 'setupadmin' role figuring that if he had the ability to create linked servers, surely he would be able to ACCESS them! Still didn't work, but after I removed that off of his login, I was unable to connect to any of my linked servers.
I know I didn't mess up the Server itself, as the SSIS ETL process can still access remote connections (such as our as400), however when attempting to use SSMS I get linked server memory errors whether I am using an IBM OLE DB connection, or an Excel connection.
On a hunch I decided to install a named instance, and sure enough I can connect fine to these sources on the Named instance, but still nothing on the default instance.
Did I mess something up when adding the setupadmin role and then removing it?
Link to my blog http://notyelf.com/
December 29, 2009 at 4:23 pm
No ideas on this at all?!
Wow I like blowing stuff up so well that even the experts are silent 🙂
Link to my blog http://notyelf.com/
December 29, 2009 at 5:10 pm
Hi,
What are the security properties of the linked server? What message is he viewing when he is triying to access?
Regards,
Jose Santiago Oyervides.
December 29, 2009 at 5:22 pm
It's not him anymore that is the problem it is ME...the administrator to the entire server...
I was able to resolve his issue after some research but it was after I dropped him from the setupadmin role that everything fell apart.
Link to my blog http://notyelf.com/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply