July 17, 2008 at 8:42 am
Hi there!
We have some users on a SQL 2000 instance who have the TARGETSERVER role in MSDB that gives them the ability to see scheduled jobs.
If viewing jobs using Enterprise Manager everything works great for these users.
However, as we are using SQL 2005 more & more, these users just want to use Management Studio to connect to either SQL2000 or SQL2005 instances but if they try to view the jobs on the SQL2000 instance with MS SSMS they get the following error pop up:
Microsoft SQL Server Management Studio
Failed to retrieve data fro this request (Microsoft.SqlServer.SmoEnum)
Additional Information
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
A severe error occurred on the current command. The results, if any should be discarded.
(Microsoft SQL Server)
I created a test user and can reproduce the problem.
Giving the test user SA solves the problem so it's some sort of permissions issue.
Obviously I don't want to give users SA so does anyone have an idea how to get around this (other than the workaround of having both SQL2K & SQL2K5 client tools installed)?
Cheers!
Stu
July 17, 2008 at 9:22 am
Take a look at "SQL Server Agent Fixed Database Roles" in Books Online. That should give you what you need to fix this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply