There is a query that our BI team use. They can not execute the query because of the error below.

Ad hoc access to ole db provider 'MSDASQL' has been denied. You must access this provider through a linked server.

I granted the users to sysadmin role and they could execute the query.

But, i don't want to give sysadmin role to them. I tried to give them bulkadmin role but it did not work.

How can i do it without sysadmin role? The query is below :

create table #temp 
(username varchar(100), 
    id varchar(100), 
    ipaddress varchar(100), 
    ipaddress2 varchar(100),  
    time_todate varchar(100), 
    username_new varchar(80),
    portnumber varchar(100))  

insert  into #temp (username , id, ipaddress, ipaddress2 , time_todate,portnumber )

 select * from OPENROWSET('MSDASQL', 'DSN=PROXY', 
 'SELECT username, id,ipaddress ,ipaddress2,time_todate,portnumber 
    FROM session nolock 
    where portnumber in (''4589ERC-13#228:154'')' ) 

 select * from #temp;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.