Could not find object '[databasename].objectname' or you do not have permission. [SQLSTATE 42000] (Error 15165)

Question

Could not find object '[databasename].objectname' or you do not have permission. [SQLSTATE 42000] (Error 15165)

tt-615680

SSCertifiable

Points: 7835
More actions
July 18, 2018 at 8:47 am

#358441

Hi All,
I have an sql agent job which goes through some of the stored procedures on diffrent databases, the service account has a sysadmin permission on the server but the jobs goes through and find some of the stored procedures and it is fine until half way through the process and throws the following error:
"Could not find object '[databasename].objectname' or you do not have permission. [SQLSTATE 42000] (Error 15165)" which doesn't make sense as it is not a permission issue
Has anyone had the same issue and know what the resolution is please?
Thank you in advance!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

tt-615680 - Wednesday, July 18, 2018 8:47 AM
Hi All,
I have an sql agent job which goes through some of the stored procedures on diffrent databases, the service account has a sysadmin permission on the server but the jobs goes through and find some of the stored procedures and it is fine until half way through the process and throws the following error:
"Could not find object '[databasename].objectname' or you do not have permission. [SQLSTATE 42000] (Error 15165)" which doesn't make sense as it is not a permission issue
Has anyone had the same issue and know what the resolution is please?
Thank you in advance!

So how and what exactly is this agent job doing? From what you have posted I have no idea where to even start.
Remember, we can't see what you see so you really need to provide more information.

tt-615680 SSCertifiable Points: 7835 More actions · Answer 2

Lynn Pettis - Wednesday, July 18, 2018 8:56 AM
So how and what exactly is this agent job doing? From what you have posted I have no idea where to even start.
Remember, we can't see what you see so you really need to provide more information.

Thank you for your reply!

The code is trying to find the processes which take too log to execute.
here is some of the code .....
SELECT TOP 100 [Runs]
    , [Avg time]
    , [Last time]
    , [Time Deviation]
    , [% Time Deviation]
    , [Last IO]
    , [Avg IO]
    , [Last IO] - [Avg IO] AS [IO Deviation]
    , [DatabaseName]
INTO #temp2
FROM #temp1
ORDER BY [% Time Deviation] DESC

SELECT DISTINCT
    ' EXEC sp_recompile ' + '''' + '[' + [DatabaseName] + '].'
    + [mydb].dbo.MyProc([SomeQuery]) + '''' AS recompileRoutineSQL
INTO #temp3
FROM #temp2
WHERE [DatabaseName] NOT IN ('master', 'msdb', 'model','tempdb')

DECLARE @RecompilationSQL NVARCHAR(MAX)
SET @RecompilationSQL = ''

SELECT @RecompilationSQL = @RecompilationSQL
+ recompileRoutineSQL + CHAR(10)
FROM #RecompileQuery
WHERE recompileRoutineSQL IS NOT NULL

DECLARE @StartOffset INT
DECLARE @Length INT
SET @StartOffset = 0
SET @Length = 4000

WHILE (@StartOffset < LEN(@RecompilationSQL))
BEGIN
PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)
SET @StartOffset = @StartOffset + @Length
END

PRINT SUBSTRING(@RecompilationSQL, @StartOffset, @Length)

EXECUTE sp_executesql @RecompilationSQL

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What does this function do: [MyDB].dbo.MyProc.
Where does this column come from: [SomeQuery].