Member Avatar for Tank50

HI

I want to create new sql account in sql server 2005,and user only able view sql agent job history details only.How do I do this.Please help.

Thanks
Tank50

  • 2 Contributors
  • 2 Replies
  • 114 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Tank50

Dani AI

Generated

Good catch, — mapping the login to msdb and choosing an Agent role is the right approach. One important clarification: SQLAgentUserRole only lets a user see and manage jobs they own. If the requirement is to let the user view job history for all jobs (but not change them), assign SQLAgentReaderRole instead. SQLAgentOperatorRole grants broader operational rights (start/stop/run jobs, manage operators), so avoid that unless you want those capabilities.

Quick steps (GUI):

  • Create the server login (Windows or SQL auth) under Security > Logins.
  • In the login properties go to User Mapping, check the msdb database and assign the appropriate role (SQLAgentUserRole or SQLAgentReaderRole).
  • Confirm the user can open SQL Server Agent > Jobs and use View History.

T-SQL examples (run as a user with permissions):

-- Windows login
CREATE LOGIN [DOMAIN\UserName] FROM WINDOWS;
USE msdb;
CREATE USER [DOMAIN\UserName] FOR LOGIN [DOMAIN\UserName];
EXEC sp_addrolemember 'SQLAgentReaderRole', 'DOMAIN\UserName';
-- SQL login
CREATE LOGIN [sqluser] WITH PASSWORD = 'StrongP@ssw0rd';
USE msdb;
CREATE USER [sqluser] FOR LOGIN [sqluser];
EXEC sp_addrolemember 'SQLAgentReaderRole', 'sqluser';

Troubleshooting and cautions:

  • If the user still sees nothing, verify they are mapped to msdb and that SQL Server Agent is running.
  • Avoid adding the account to the sysadmin server role — that gives full control.
  • If finer-grained, read-only access to only the history is required, you can grant SELECT on msdb.dbo.sysjobhistory (and join to sysjobs for names), but this is more fragile across versions than using the built-in Agent roles.
  • To check membership, run the role membership query in msdb or use sp_helpuser.

Thanks to for the documentation pointers and to for confirming the GUI mapping — choose SQLAgentReaderRole if you need global history visibility.

Recommended Answers

All 2 Replies

Member Avatar for debasisdas

You need to read this and this.

Also read this.

Member Avatar for Tank50

Hi

Thanks for Reply.I found the how to do this.First create server loging sql server 2005.After that in user mapping select msdb table and select role as sqlagentuser role.

Thanks
Tank46

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.