i attempting create query accesses multiple databases on single server. using cursor access multiple other linked servers base server query run on. issue running following:
msg 916, level 14, state 1, line 43 server principal "user" not able access database "metals" under current security context. msg 3930, level 16, state 1, line 104 current transaction cannot committed , cannot support operations write log file. roll transaction. msg 916, level 14, state 1, line 43 server principal "user" not able access database "metals" under current security context. msg 3930, level 16, state 1, line 104 current transaction cannot committed , cannot support operations write log file. roll transaction. the server shows logged in, why database using "user" , "user" attempt access "metals" db?
believe query access metals db correct because returns correct data when running outside of dynamic sql code. think issue has permissions, unsure permissions should changing user. currently, bluser has connect , select permissions. there other permissions should adding allow them access metals db?
the query follows:
declare @location varchar(50) declare @srv varchar(20) declare @alphadb varchar(20) declare labcursor cursor select location, sqlserver, alphadb labs open labcursor declare @sql varchar(max) create table #tmpcombinedresults ( lab varchar(50) null, department varchar(50) null, instrument varchar(50) null, method varchar(50) null, matrix varchar(50) null, studydate datetime null, studynumber int null ) fetch next labcursor @location, @srv, @alphadb while @@fetch_status = 0 begin -- query both metals , alpha set @sql = ' create table #tmpresults ( lab varchar(50) null, department varchar(50) null, instrument varchar(50) null, method varchar(50) null, matrix varchar(50) null, studydate datetime null, studynumber int null ) insert #tmpresults(department, instrument, method, matrix, studydate, studynumber) select t.dept, oms.instrumentid, oms.method, oms.matrix, max(oms.dateofstudy) studydate, oms.studynum [' + @alphadb + '].[dbo].analrunseq ars inner join [' + @alphadb + '].[dbo].ottmdl1studies oms on ars.testno = oms.method inner join [' + @alphadb + '].[dbo].tests t on ars.testcode = t.testcode inner join [' + @alphadb + '].[dbo].analruns ar on ars.runid = ar.runid , oms.instrumentid = ar.instrumentid , oms.analyst = ar.analyst inner join [' + @alphadb + '].[dbo].instruments on oms.instrumentid = i.instrumentid oms.activestudy <> 0 , oms.typeofstudy = ''mdl'' group oms.instrumentid, oms.method, oms.matrix, oms.studynum, t.dept, i.inactive having t.dept not in (''sub-org'',''sub'',''subpr'') , i.inactive = 0 order oms.instrumentid --error occurs in part of code if (select count(*) qty ' + @srv + '.master.sys.databases name = ''metaldata'') > 0 begin update #tmpresults set department = ''me'', instrument = ms.instrumentid, method = ms.testno, matrix = ms.matrix, studydate = (select max(ms.inusedate) [metals].[dbo].mdlstudies ms ms.inusedate = inusedate) #tmpresults tmp inner join ' + @srv + '.[metals].[dbo].mdlstudies ms on tmp.instrument = ms.instrumentid ms.active = 1 end select ''' + @location + ''' lab, department, instrument, method, matrix, studydate, studynumber #tmpresults order lab, department, instrument drop table #tmpresults ' if db_name() <> @alphadb begin set @sql = 'exec(''' + replace(@sql, '''', '''''') + ''') @ ' + @srv end insert #tmpcombinedresults exec(@sql) fetch next labcursor @location, @srv, @alphadb end close labcursor deallocate labcursor select * #tmpcombinedresults drop table #tmpcombinedresults the current solutions have found on web, outlined here didn't work for me either. one in particular didn't make sense because don't see databases option in left column of object explorer details.
any solve issue appreciated!
this issue caused user not having proper permissions in 2 of 5 servers being referenced cursor. once permissions corrected, issue resolved.
Comments
Post a Comment