25112
asked on
equivalent of 'SQL Server Agent Fixed Database Roles' in SQL 2000
how do you assign Agent roles in SQL 2000?
I do not recall it being much different than assigning roles to users in SQL 2005.
Here are some references that may help:
http://technet.microsoft.com/en-us/library/cc966453.aspx
http://www.informit.com/articles/article.aspx?p=23574
Here are some references that may help:
http://technet.microsoft.com/en-us/library/cc966453.aspx
http://www.informit.com/articles/article.aspx?p=23574
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
no problem- thanks for trying..
how would you interpret the following:
http://msdn.microsoft.com/en-us/library/aa213804%28v=sql.80%29.aspx
does that mean anyone with db_datawriter permission will be able to access the agent jobs?
how would you interpret the following:
http://msdn.microsoft.com/en-us/library/aa213804%28v=sql.80%29.aspx
does that mean anyone with db_datawriter permission will be able to access the agent jobs?
My interpretation of that was that permissions check is related to writing of log files (to the file system). The final note indicates that the SQL Agent account does this, but that check ensures that user actually has permissions ... my take is for auditing of escalation of privileges, i.e., I cannot write to file, but I own a job that can. See the link I posted in http:#36920308 -- the tables there show the specific permissions for each of the three fixed server roles for SQL Agent that you could just replicate in SQL 2000 and assign to specific users.
ASKER
>>the tables there show the specific permissions for each of the three fixed server roles for SQL Agent that you could just replicate in SQL 2000 and assign to specific users.
when i run
use msdb
EXECUTE sp_addrolemember @rolename = 'SQLAgentReaderRole ', @membername = 'decla\jlu'
in 2000, i get
Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 37
The role 'SQLAgentReaderRole ' does not exist in the current database.
when i run
use msdb
EXECUTE sp_addrolemember @rolename = 'SQLAgentReaderRole ', @membername = 'decla\jlu'
in 2000, i get
Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 37
The role 'SQLAgentReaderRole ' does not exist in the current database.
ASKER
would you have any input on TargetServersRole role?
i just found
http://www.sql-server-performance.com/2006/sqlagent-scheduled-jobs/
it says "in SQL Server 2000 the DBA must add the user to TargetServersRole role in MSDB database. Prior to Service Pack 3 on SQL Server 2000 the user must be added to the sysadmin group in order to get a chance to view the jobs that are owned by sysadmin group."
so, then, why am i not able to find any msdn docs on 'TargetServersRole'..'
i just found
http://www.sql-server-performance.com/2006/sqlagent-scheduled-jobs/
it says "in SQL Server 2000 the DBA must add the user to TargetServersRole role in MSDB database. Prior to Service Pack 3 on SQL Server 2000 the user must be added to the sysadmin group in order to get a chance to view the jobs that are owned by sysadmin group."
so, then, why am i not able to find any msdn docs on 'TargetServersRole'..'
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks for your helpful feedback!
ASKER
>>And now I have SQL 2005 or higher only, though still only sysadmins create SQL agent jobs within my organization.
that means, you do not use any of the SQL Server Agent Fixed Database Roles in your organization, right?
that means, you do not use any of the SQL Server Agent Fixed Database Roles in your organization, right?
Correct. Our sysadmins handle scheduling and manipulation of SQL jobs. We do use other fixed database roles and some application roles for authorization into specific databases and tables. So I can vouch that roles based management works pretty well. :)
Greg reminded me of a good point here -- https://www.experts-exchange.com/questions/27384208/SQL-Server-2008-Role-to-Submit-Jobs.html -- the SQL Server Agent Fixed Database Roles and TargetServersRole are all in msdb System Database. That is where the SQL jobs are as he said, so if you are doing this manually...this fact may come in handy.
ASKER
thanks for clarrifying, Kevin..
>>so if you are doing this manually...this fact may come in handy.
is there any alternative to doing it manually?
>>.this fact may come in handy.
amen.. many thanks.
>>so if you are doing this manually...this fact may come in handy.
is there any alternative to doing it manually?
>>.this fact may come in handy.
amen.. many thanks.
ASKER