Avatar of dfr031260
dfr031260
 asked on

Read system tables permissions in SQL 2008 R2

Folks,

I need to grant just read or select rights to system tables to a role or user.  Specifically I need this role to be able to run...:

select s.name,
       case
           when l.name is null then 'CIQDEV\sqlagentadmin'
           else l.name
       end as JobName
 from  msdb..sysjobs s
 left join master.sys.syslogins l on s.owner_sid = l.sid
 where s.enabled = 0
   and (s.name not like '_D%')
   and (s.name not like '_T%')
 order by s.name

and

SELECT JOB.NAME AS JOB_NAME,
STEP.STEP_ID AS STEP_NUMBER,
STEP.STEP_NAME AS STEP_NAME,
STEP.COMMAND AS STEP_QUERY,
DATABASE_NAME
FROM Msdb.dbo.SysJobs JOB
INNER JOIN Msdb.dbo.SysJobSteps STEP ON STEP.Job_Id = JOB.Job_Id
WHERE JOB.Enabled = 1
AND (STEP.COMMAND LIKE '%sp_MS%')--JOB.Name like '%NetHouse%')-- OR STEP.COMMAND LIKE '%Exec AnotherStoredProcedure%')
ORDER BY JOB.NAME, STEP.STEP_ID
GO

Any system catalog or view dealing with SQL Server Agent Jobs
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Scott Pletcher

8/22/2022 - Mon
SThaya

Put them on the public role, and only give SELECT permission to the tables/views you want.
ASKER CERTIFIED SOLUTION
SThaya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dfr031260

ASKER
So there is no SQL Server provided role for this?
Scott Pletcher

I suggest:
1) re-writing the first query so they don't need access to syslogins (which is a deprecated view anyway).
2) add the user (or role) to the msdb role "SQLAgentReaderRole" (this should work, although I can't guarantee it; but it's worth a try, since it allows you to use standard roles instead of creating your own custom one).


1)
select j.name, isnull(SUSER_SNAME(j.owner_sid), 'CIQDEV\sqlagentadmin') AS JobName
 from  msdb..sysjobs j
 where j.enabled = 0
   and (j.name not like '_D%')
   and (j.name not like '_T%')
 order by j.name

2)
USE msdb
CREATE USER '<user_name>' FROM '<login_name>'
EXEC sp_addrolemember 'SQLAgentReaderRole', '<user_name>'
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
dfr031260

ASKER
Can I execute...:

grant select on msdb.dbo.sysjobs to dba_role;
grant select on msdb.dbo.sysjobhistory to dba_role;
grant select on  msdb.dbo.sysjobschedules to dba_role;
GO
Scott Pletcher

Of course you can; if you prefer, try that and see if it works.