Insert records into temp table from msdb.dbo.syssessions

trnvg8h3  于 2023-02-28  发布在  其他
关注(0)|答案(1)|浏览(91)

I don't have access to msdb.dbo tables so I am trying to insert records in temp table

Example

For msdb.dbo.sysjobactivity I did as

Create table #Sysjobactivity (All columns here)

Insert into #sysjobactivity

Execute msdb.dbo.sp_help_jobactivity---worked fine for this

But for msdb.dbo.syssessions I am unable to insert

Create table #syssessions(all columns here)

Insert into #syssessions

execute msdb.dbo.help_sessions

But I can't see any msdb.dbo.help_sessions , is there a way we can insert

Because of an access issue, I am trying to go through this route.

v8wbuo2f

v8wbuo2f1#

The problem seems to be that you are not in the sysadmin fixed server role, which is listed in the documentation as a requirement for reading syssessions directly.

This is not a problem we can solve for you.

There is no easy way to "get around" a sysadmin required security restriction unless someone is willing to give you sysadmin (which I doubt and don't recommend anyway).

I'm not sure where you read about help_sessions but that is not a stored procedure that ships with SQL Server, so that is why you can't find it.

Now, you can have someone with appropriate access create this procedure for you:

USE msdb;
GO

CREATE PROCEDURE dbo.help_sessions
WITH EXECUTE AS OWNER
AS
  SELECT session_id, agent_start_date
    FROM dbo.syssessions;
GO

GRANT EXECUTE ON dbo.help_sessions 
  TO [your explicit user or whatever role(s) you're in];'

But I suspect you won't be able to do that yourself.

That said, since we've now learned that you just want to know when SQL Server Agent last started:

SELECT login_time
  FROM sys.dm_exec_sessions
  WHERE program_name = N'SQLAgent - Generic Refresher';

Which doesn't require sysadmin , only VIEW SERVER STATE (see the docs).

相关问题