June 26, 2006 at 2:45 pm
?
June 27, 2006 at 5:36 am
Basically: Create a new role in MSDB, set permissions for that role, modify existing sysjobs_view to include the new role, and add the user to the MSDB role.
Script to use to create role, and set permissions:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
USE msdb
GO
EXEC sp_addrole N'JobStatus'
GO
DENY EXECUTE ON [dbo].[sp_add_job] TO JobStatus
DENY EXECUTE ON [dbo].[sp_delete_job] TO Jobstatus
GO
GRANT SELECT ON [dbo].[sysjobhistory] TO Jobstatus
GRANT SELECT ON [dbo].[sysjobs] TO Jobstatus
GRANT SELECT ON [dbo].[sysjobs_view] TO Jobstatus
GRANT SELECT ON [dbo].[sysjobschedules] TO Jobstatus
GRANT SELECT ON [dbo].[sysjobsteps] TO Jobstatus
GO
DROP VIEW sysjobs_view
GO
CREATE VIEW sysjobs_view
AS
SELECT *
FROM msdb.dbo.sysjobs
WHERE (owner_sid = SUSER_SID())
OR (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'TargetServersRole'), 0) = 1)
OR (ISNULL(IS_MEMBER(N'JobStatus'), 0) = 1)
GO
The GRANT and DENY lines are just examples. Play with it to find the correct permissions for your user.
After you've done that : add the user to the new MSDB Role, and it works.
June 29, 2006 at 3:03 pm
Hello,
I have the same issue. I have a new role in the msdb database now. Does the user have to be in the msdb database? I have the user in the users list of the server but I can't see him to add him to the new role in msdb. Does the user have to live in both places? Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply