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

how do you assign Agent roles in SQL 2000?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.