troubleshooting Question

Read system tables permissions in SQL 2008 R2

Avatar of dfr031260
dfr031260 asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
6 Comments1 Solution1112 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
SThaya
Technical MAnager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros