Get Sharepoint List Access from SQL Server

Well, here I go – how to natively (or naively at this point – I just started this) manipulate Sharepoint Lists from SQL Server.

I did it! It is not using a linked server (I will get to the bottom of that though) but using an OPENROWSET works. This means that the user needs to have insert and select privileges – but there is a way around that too – by making a role/user internal to the dB and running the stored proc run (EXECUTE AS) that role/user. To get that to work takes some tinkering – another article will come soon regarding that.

Basically – the onerous method would show that the two statements below work on an integrated networked environment – this was done FROM Sql Server (SSIS). Connection strings for sharepoint are from this site and the guy who suggested it is WAAAY down the comments list on this article (look for Don Vineyard’s comment). Note that IMEX=1 means read only.

select '1',*
OPENROWSET ('Microsoft.ACE.OLEDB.12.0','WSS;IMEX=1;RetrieveIds=Yes;User id=MYDOMAIN\username;Password=*********;DATABASE=;LIST={1d2d13c4-08cd-4068-8cc1-58b5d6d739e0};',

select '2',*
from OPENROWSET ('Microsoft.ACE.OLEDB.12.0','WSS;IMEX=1;RetrieveIds=Yes;DATABASE=;LIST={1d2d13c4-08cd-4068-8cc1-58b5d6d739e0};'

Now that is quite basic. OPENROWSET does not accepta dynamic string so …

DECLARE @listnumber VARCHAR(100) = '2719FBBB%2D1C7F%2D4D64%2DAF55%2DC3E3A83378A8' --note I copied and pasted this from the URL
/* convert the url version to something usable - lets be lazy and let the computers do th work - make a function for this of course */
SET @listnumber = REPLACE(@listnumber,'%7B','')
SET @listnumber = REPLACE(@listnumber,'%2D','-')
SET @listnumber = REPLACE(@listnumber,'%7b','')
SET @listnumber = REPLACE(@listnumber,'%2d','-')

PRINT @listnumber

@user VARCHAR(50) = 'DOMAIN\my.username'
, @password VARCHAR(50) = 'my_password'
, @database VARCHAR(255) = ';LIST={' + @listnumber + '}'

/* Make the select statement being careful to use two single quotes '' when one is needed */
SET @longSQL = N'select ''1'',*
OPENROWSET (''Microsoft.ACE.OLEDB.12.0'',''WSS;IMEX=1;RetrieveIds=Yes;User id=' + @user + ';Password=' + @password + ';DATABASE=' + @database + ';'',


EXEC sp_executesql @longSQL


As for getting it to be a linked server .. this article is a start that I still have to investigate …

Others I have to vet are:

Links So Far:

Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

Please answer the question so the system knows your a human and not a web-bot * Time limit is exhausted. Please reload CAPTCHA.