I am attempting to create a query that accesses multiple databases on a single server. I am using a cursor to access multiple other linked servers to the base server that the query is run on. The issue that I am running into is the following:
Msg 916, Level 14, State 1, Line 43
The server principal "USER" is not able to access the database "Metals" under the current security context.
Msg 3930, Level 16, State 1, Line 104
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 916, Level 14, State 1, Line 43
The server principal "User" is not able to access the database "Metals" under the current security context.
Msg 3930, Level 16, State 1, Line 104
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The server shows that I am logged in, so why is the database using "USER" and "User" to attempt to access the "Metals" db?
I believe that the query to access the Metals db is correct because it returns the correct data when running it outside of the dynamic sql code. I think the issue has to do with permissions, but I am unsure which permissions I should be changing for the user. Currently, BLUser only has connect and select permissions. Are there other permissions that I should be adding to allow them to access the Metals db?
The query is as follows:
DECLARE @location as varchar(50)
DECLARE @srv as varchar(20)
DECLARE @alphaDb as varchar(20)
DECLARE LabCursor Cursor FOR
SELECT Location, SQLServer, AlphaDB
FROM Labs
OPEN LabCursor
DECLARE @sql as 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 FROM LabCursor INTO @location, @srv, @alphaDb
WHILE @@FETCH_STATUS = 0
BEGIN
-- query with both metals and 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 INTO #tmpResults(Department, Instrument, Method, Matrix, StudyDate, StudyNumber)
SELECT t.Dept,
oms.InstrumentID,
oms.Method,
oms.Matrix,
MAX(oms.DateOfStudy) StudyDate,
oms.StudyNum
FROM [' + @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
AND oms.InstrumentID = ar.InstrumentID
AND oms.Analyst = ar.Analyst
INNER JOIN [' + @alphaDb + '].[dbo].Instruments i ON oms.InstrumentID = i.InstrumentID
WHERE oms.ActiveStudy <> 0
AND oms.TypeOfStudy = ''MDL''
GROUP BY oms.InstrumentID,
oms.Method,
oms.Matrix,
oms.StudyNum,
t.Dept,
i.InActive
HAVING t.Dept Not In
(''sub-org'',''sub'',''subpr'')
AND i.InActive = 0
ORDER BY oms.InstrumentID
--error occurs in this part of the code
IF (SELECT COUNT(*) as Qty FROM ' + @srv + '.master.sys.databases where name = ''MetalData'') > 0
BEGIN
UPDATE #tmpResults
SET Department = ''ME'',
Instrument = ms.InstrumentID,
Method = ms.TestNo,
Matrix = ms.Matrix,
StudyDate = (SELECT MAX(ms.InUseDate)
FROM [Metals].[dbo].MDLStudies ms
WHERE ms.InUseDate = InUseDate)
FROM #tmpResults tmp
INNER JOIN ' + @srv + '.[Metals].[dbo].MDLStudies ms ON tmp.Instrument = ms.InstrumentID
WHERE ms.Active = 1
END
SELECT ''' + @location + ''' AS Lab,
Department,
Instrument,
Method,
Matrix,
StudyDate,
StudyNumber
FROM #tmpResults
ORDER BY Lab, Department, Instrument
DROP TABLE #tmpResults
'
IF DB_NAME() <> @alphaDb
BEGIN
SET @sql = 'EXEC(''' + REPLACE(@sql, '''', '''''') + ''') at ' + @srv
END
INSERT INTO #tmpCombinedResults
EXEC(@sql)
FETCH NEXT FROM LabCursor INTO @location, @srv, @alphaDb
END
CLOSE LabCursor
DEALLOCATE LabCursor
SELECT *
FROM #tmpCombinedResults
DROP TABLE #tmpCombinedResults
The current solutions that I have found on the web, all outlined here didn't work for for me either. This one in particular didn't make sense because I don't see Databases as an option in the left column of the Object Explorer Details.
Any help to solve this issue would be greatly appreciated!
