• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 840
  • Last Modified:

equivalent of 'SQL Server Agent Fixed Database Roles' in SQL 2000

how do you assign Agent roles in SQL 2000?
0
25112
Asked:
25112
  • 7
  • 6
2 Solutions
 
25112Author Commented:
example: SQLAgentUserRole , SQLAgentReaderRole
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
Ah, I am sorry, I see you are talking about the fixed server roles, specifically.
http://msdn.microsoft.com/en-us/library/ms188283(v=SQL.90).aspx

I initially read this as how to assign roles in SQL 2000 not paying attention to the question title. Yes, those roles were introduced in SQL 2005. My point above was you could create your own roles in SQL 2000 the same as in newer versions, so maybe you can just create roles and assign equivalent rights to what is in the documentation linked in this post. I never tried it and have not been on SQL 2000 for a while...sorry to have posted without fully catching all the details.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
25112Author Commented:
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?
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
25112Author Commented:
>>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.
0
 
25112Author Commented:
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'..'
0
 
Kevin CrossChief Technology OfficerCommented:
Regarding http:#36925952, maybe I am not being clear. I was trying to explain earlier that "those roles were introduced in SQL 2005. My point above was you could create your own roles in SQL 2000 the same as in newer versions, so maybe you can just create roles and assign equivalent rights to what is in the documentation linked in this post." So, you have to create those roles and assign the same rights that are listed in the table for each of the three roles introduced in SQL 2005.

I was always a sysadmin and cannot think of a scenario where I had users needing access to SQL jobs that did not flow through a DBA; therefore, I never really used TargetServersRole. And now I have SQL 2005 or higher only, though still only sysadmins create SQL agent jobs within my organization.
0
 
25112Author Commented:
thanks for your helpful feedback!
0
 
25112Author Commented:
>>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?
0
 
Kevin CrossChief Technology OfficerCommented:
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. :)
0
 
Kevin CrossChief Technology OfficerCommented:
Greg reminded me of a good point here -- http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_27384208.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.
0
 
25112Author Commented:
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.
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now