SQL 2005 SQLAgentReaderRole Securables Missing?

[fuzzyocelot]

Hello again!

Maybe it’s just something I’m not understanding, but when I view the securables for the SQLAgentReaderRole via Management Studio, I see nothing. There is no list. Is there something wrong with my installation or is there some way I can view the securables for this role? Or is there something I’m just not understanding?

Thanks!

 

[mrdenny]

When viewing the list of objects for any user or role the list will always start as blank. You have to add in the objects you want to see the permissions for. This thread thread962-1324602 has some scripts which I posted which will allow you to see the rights that user or role has.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

Thank you for the information! I used the 2005 query you posted in that thread. I can see the list of objects for the SQLAgentUserRole and the SQLAgentOperatorRole but there is nothing for the SQLAgentReaderRole. Since these roles came with SQL 2005, shouldn't the Reader role have pre-assigned permissions on certain objects?

 

[mrdenny]

Yes it should, unless someone has removed the rights.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

The strange thing is it's the same on both of our SQL 2005 servers. The other servers are SQL 2000. I even checked my local installation. It's the same as the servers. They all have SP2 installed. So it looks like we did something during the installations, but I don't know what.

 

[mrdenny]

My system doesn't seam to have any rights to that role either.

I'm not seeing any rights to any objects that are being granted to the public role either which is kind of strange.

What was the initial problem which prompted you to post your initial question?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[fuzzyocelot]

I started looking into the msdb roles because one of our developers couldn't do much with the packages she had deployed to the server and with the jobs she had created.

With Integration Services (IS) on the server, she couldn't see the packages. So I believe I added her to the SQLAgentUserRole. Unfortunately, I didn't write it down so I'm not 100% sure. When she created jobs for the packages, she couldn't change or delete the schedule even though she was the owner. I can't remember which role I granted her rights to first. It was probably the SQLAgentUserRole, then Reader, and then finally Operator because she kept getting permission errors. Once I added her to the Operator role, she stopped getting errors. I wish I had taken better notes on which roles helped her through which errors. Unfortunately, I'm not sure. So that's how I started looking into the roles deeper. I don't know how much it helps, if at all.

Thanks,
Rebecca