CODEWITHSUNDEEP
sql-server-2008
BogdanM
BogdanM

Reputation: 997

User access to SQL Server Agent - job activity monitor

I need to check some job that ran on a SQL Server 2008, but the user used when logging on to that server can not have sysadmin rights.

So my question is can I have an user that can access SQL Server Agent - Job Activity Monitor, but that user is not in the sysadmin group?

Upvotes: 0

Views: 4529

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

Add the user to msdb and put them in the SQLAgentOperatorRole fixed database role. For more information see the Books Online topic How to: Configure a User to Create and Manage SQL Server Agent Jobs.

In T-SQL, this would be:

USE msdb;
GO
CREATE USER UserName FROM LOGIN [UserName];
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'UserName';
GO

In SQL Server 2012 that sp_addrolemember should not be used; instead:

ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [UserName];

Upvotes: 1

Related Questions