Link to home
Start Free TrialLog in
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
Avatar of SThaya
SThaya
Flag of India image

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

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dfr031260
dfr031260

ASKER

So there is no SQL Server provided role for this?
Avatar of 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>'
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
Of course you can; if you prefer, try that and see if it works.