When we try to access the SQL Server Agent related system tables, The following Error occurred
Msg 229, Level 14, State 5, Line 1
The SELECT permission was denied on the object 'sys...', database 'msdb', schema 'dbo'.
What are the Database role needed to access the following SQL Server Agent system tables ?
USE MSDB
Go
The following system tables needed SQLAgentOperatorRole database role
sysalerts,
sysnotifications,
sysoperators
The following system table needed SQLAgentUserRole database role
syscategories
The following system tables needed TargetServersRole database role
sysdownloadlist,
sysjobs,
sysjobservers,
systargetservers,
syssubsystems
The following system tables needed db_Owner database role
sysjobactivity,
sysjobhistory,
sysjobschedules,
sysjobsteps,
sysjobstepslogs,
systargetservergroupmembers,
systargetservergroups,
systaskids,
sysproxies,
sysproxylogin,
sysproxysubsystem,
sysschedules,
syssessions
Granting Database Role to a Login:
USE msdb
GO
EXEC sp_addrolemember N'RoleName', N'Login'
Hi,
ReplyDelete> When I go on my production server -->database, right click, properties, it gives an error:
>
> cannot show requested dialog.
> Property owner is not available for database 'XYZ'. This property may not exist for this object, or may not be retrievable due to insufficient rights.
>
> I am using sa and have all rights.
> Other databases I can see the properties but only two of them are giving these errors.