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;