March 26, 2012 at 9:44 am
I created a schema I called MgmtRpt, so that I could group stored procedures related to reports for management into it. Then I created some stored procedures in that schema, tested them, and everything works fine.
Now another developer wants to be able to write either a SSRS report, or a Visual Studio 2010 report application, against those stored procedures, so the managers can have their reports on demand. Looks like a win-win all around.
Only problem, SSRS doesn't see my new MgmtRpt schema. Nor does VS 2010. Why is that? What have I left out? If SSRS (or VS 2010) isn't capable of seeing anything other than dbo, then what's the point of schemas?
Kindest Regards, Rod Connect with me on LinkedIn.
March 27, 2012 at 1:53 am
what security rights have you assigned to the schema and also what rights do the users have to the schema.
for example, SSRS uses a dedicate account to connect to, if that account hasnt got execute rights to the schema or db_owner to the database it wont see the schema as its security rights are insufficient
March 27, 2012 at 10:00 am
You've hit upon it. I was using a SQL role someone here created in the past called "mis". It owned the MgmtRpt schema that I created (I assigned mis as the owner). I just today discovered what the problem was. In our Active Directory we have an organizational unit called "MIS", which I thought was what was in the SQL role "mis". Today I discovered that whoever created that mis role way back in the day, hard-coded all of the users that were around back then. AAARRRGGGGHHHH!!! So, I changed to owner of my new MgmtRpt schema to something I think is more appropriate.
I am curious to know what owner SSRS runs under.
Kindest Regards, Rod Connect with me on LinkedIn.
March 28, 2012 at 1:42 am
Rod at work (3/27/2012)
I believe I may have been rude with the initial post of this thread. If in fact you found my initial post to be rude, then I am truly sorry and ask your forgiveness.
Rude not at all.
In terms of what Owner SSRS runs under what do you mean? It runs under what ever account you have specified in the data source to connect to SQL, so if that account cannot see the schema, then it cant see the procs. Goes back to security basics, you can only see what you have permission to see.
March 28, 2012 at 8:08 am
anthony.green (3/28/2012)
In terms of what Owner SSRS runs under what do you mean? It runs under what ever account you have specified in the data source to connect to SQL, so if that account cannot see the schema, then it cant see the procs. Goes back to security basics, you can only see what you have permission to see.
Oh, OK, I thought you meant that whenever installing SSRS all reports within it ran from the same account, probably some network account. I wasn't sure what account that was, so I asked.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply