Cannot set job alerts on Notifications tab if a non-sysadmin user

[Dipak Patel]

Platform: Win2000 SP3, SQL 7.00 - 7.00.1063

I have a SQL-authenticated user with the following permissions:
"Process Administrators" server role
"db_owner" and "TargetServersRole" for msdb database
"db_owner" for master database.

The problem is that when this user opens up any job (i.e. owned by any
user) in the SQL Server Agent, and goes to the Notifications tab, the
first three alerts (Email, Page and Net send) are greyed out, i.e.
these cannot be set.
The other options are available (Write to Windows application event
log, Automatically delete job).

The only way I can achieve this is to give this user the "System
Administrators" server role, i.e. sa.
But this of course gives absolutely full access, which I don't want.
Is there a way for a user to see/change Notifications of jobs which
only he owns?

[Simon Hayes]

di*****@hotmail.com (Dipak Patel) wrote in message news:<30**************************@posting.google. com>...

Platform: Win2000 SP3, SQL 7.00 - 7.00.1063

I have a SQL-authenticated user with the following permissions:
"Process Administrators" server role
"db_owner" and "TargetServersRole" for msdb database
"db_owner" for master database.

The problem is that when this user opens up any job (i.e. owned by any
user) in the SQL Server Agent, and goes to the Notifications tab, the
first three alerts (Email, Page and Net send) are greyed out, i.e.
these cannot be set.
The other options are available (Write to Windows application event
log, Automatically delete job).

The only way I can achieve this is to give this user the "System
Administrators" server role, i.e. sa.
But this of course gives absolutely full access, which I don't want.
Is there a way for a user to see/change Notifications of jobs which
only he owns?

Unfortunately, only sysadmins can modify jobs belonging to other
users. If you want to make sure that all jobs have a standard set of
notifications, one solution might be to create an "add notifications"
job which loops through all users' jobs, and adds your notifications
to them. Or you could create your own modified versions of the msdb
stored procedures, without the check for sysadmin, but that would be a
more drastic solution, and more difficult to maintain.

Simon

[Dipak Patel]

thanks, yes I agree with you Simon, I think its a slight limitation of
SQL Server. I'll just manage the notifications centrally by sysadmins.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!