Overriding System Security

Hey folks!
First, if I am understanding correctly, even though I remove the
BUILTIN\Administrators from any roles and/or access in RS, they still have full
access to Report Manager, system settings and all. Is this correct?
If the above is true, has anyone found any way to circumvent this. I think this
a stupid concept. Just because a user is in the local Administrators group on a
server, doesn't mean he/she knows how to manage RS.
Thanks in advance.A local administrator can do just about anything on the box, including
format the hard drive. This does not mean the user should do it or knows
that he should not do it. That is why everyone is not an administrator.
Same applies to RS. Does the user need to be an administrator on the box?
If you remove the Builtin Administrators, they will not have full access to
RS. They can still do admin type work on RS, but will find they cannot view
reports and many other things.
--
| Date: Tue, 28 Jun 2005 10:01:41 -0500
| From: Darrell <Darrell.Wright.nospam@.okc.gov>
| User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
| X-Accept-Language: en-us, en
| MIME-Version: 1.0
| Subject: Overriding System Security
| Content-Type: text/plain; charset=ISO-8859-1; format=flowed
| Content-Transfer-Encoding: 7bit
| Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
| Lines: 1
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hey folks!
|
| First, if I am understanding correctly, even though I remove the
| BUILTIN\Administrators from any roles and/or access in RS, they still
have full
| access to Report Manager, system settings and all. Is this correct?
|
| If the above is true, has anyone found any way to circumvent this. I
think this
| a stupid concept. Just because a user is in the local Administrators
group on a
| server, doesn't mean he/she knows how to manage RS.
|
| Thanks in advance.
||||Brad Syputa - MS wrote:
> A local administrator can do just about anything on the box, including
> format the hard drive. This does not mean the user should do it or knows
> that he should not do it. That is why everyone is not an administrator.
>
Good point.
> Same applies to RS. Does the user need to be an administrator on the box?
>
There are a number of AD groups that these users are in that have admin rights
on the server so they can perform various maintenance tasks.
> If you remove the Builtin Administrators, they will not have full access to
> RS. They can still do admin type work on RS, but will find they cannot view
> reports and many other things.
As I said, the BUILTIN\Administrators was removed from RS altogether. Is there
any way, perhaps through IIS, that the local admins could have their access to
RS restricted?
> --
> | Date: Tue, 28 Jun 2005 10:01:41 -0500
> | From: Darrell <Darrell.Wright.nospam@.okc.gov>
> | User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
> | X-Accept-Language: en-us, en
> | MIME-Version: 1.0
> | Subject: Overriding System Security
> | Content-Type: text/plain; charset=ISO-8859-1; format=flowed
> | Content-Transfer-Encoding: 7bit
> | Message-ID: <ORrbOJ$eFHA.2128@.TK2MSFTNGP14.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | NNTP-Posting-Host: okcpxfw.okc.gov 205.162.227.132
> | Lines: 1
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:46889
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hey folks!
> |
> | First, if I am understanding correctly, even though I remove the
> | BUILTIN\Administrators from any roles and/or access in RS, they still
> have full
> | access to Report Manager, system settings and all. Is this correct?
> |
> | If the above is true, has anyone found any way to circumvent this. I
> think this
> | a stupid concept. Just because a user is in the local Administrators
> group on a
> | server, doesn't mean he/she knows how to manage RS.
> |
> | Thanks in advance.
> |
>

Overriding repeating totals from non-referenced slicer dimension

I've run across what appears to be a change in the default behavior of Analysis Services from 2000 to 2005. My question is how to get the default behavior to revert to the AS 2000 method.

If you have a cube with multiple measure groups or multiple linked cubes, queries that try to slice a measure using a dimension that is not related to the measure result in the total amount for any other slicers being repeated across the "invalid" slicer.

For example, the following query slices the reseller amount by calendar year and employee department. The results are as expected. Only the Sales department has any totals for the measure and all other departments return null.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Reseller Sales Amount] );

The next query is identical, except it using the Internet Sales Amount measure, which comes from a measure group that does not have a link to the Employee dimension. The result is the total amount for internet sales is displayed across all employee departments. In AS 2000, the results would have been null or empty.

SELECT { [Employee].[Employee Department].[All Employees].CHILDREN } ON COLUMNS ,{ DESCENDANTS( [Date].[Calendar].[All Periods], [Date].[Calendar].[Calendar Year] ) } ON ROWS FROM [Adventure Works] WHERE ( [Measures].[Internet Sales Amount] );

At one level I can agree with the change, but non-the-less, it is a change in the default behavior, and users hate it. Is ther any way to revert this behavior back to the AS 2000 approach?

Thanks!

What you are running into is the fact that AS2K5 now applies ValidMeasure() automatically. You can revert back to AS2K behavior by setting the "IgnoreUnrelatedDimensions" property associated with your measure group to "False".

HTH,

- Steve

|||

Steve,

Many thanks! I knew it would be something simple, but was getting tired of looking...

Overriding Defaults

I have a stored procedure that has @.BeginDate and @.EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @.BeginDate and GetDate()+10 for the @.EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'

What I assume is that if I override the default, the stored procedure will process what I put in the @.BeginDate and @.EndDate parameters.

The where statement looks like:

and (m.BeginDate >= @.BeginDate) and (m.EndDate <= @.EndDate)

I'm using Reporting Services 2005 and SQL Server 2005.

Thanks, Iris

I have figured out my own problem. In my where statement, in the stored procedure, I put in a case statement that calculates for today, yesterday, etc. for the BeginDate and EndDate parameters. I then created available values for today, yesterday, etc. I made the default for both parameters 'Today'. Then when I created my linked report from my master report, there was a drop down to select my date range options.

Thanks, Iris

Overriding any mdx statement using product line 'A' to also include product line 'B'

Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'? I started to look at the Scope keyword, but I'm at a loss for understanding it just yet.

The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Q:Using adventure works as a model,
if product line Accessory is used, can something be put in place so that product line Components is also included in its 'Scope'?

A:

You can put set in Scope:

Scope ({[Product].[Product Line].[Accessory], [Product].[Product Line].[Components]});
This = 100;
END SCOPE;

Q: The other requirement is also to only have this override be used for transactions on or after 1/9/2007. Is this a possibility as well?

Mosha fixed me here. I thought it is hard to do, but he showed in the following post.

Vidas Matelis

|||

Well, you can always do the following:

Accessory = Accessory + Components;

But this will give you double counting of Components at the All level. To make this apply after 1/9/2007 - you can do:

SCOPE [1/9/2007] : NULL;

Accessory = Accessory + Components;

END SCOPE;

|||

Using the unique name for all of those items and then adding the scope script works, thanks.

Is there anyway to tackle the 'all' value being incorrect because of the duplication?
I tried to add another scope, but it didn't help - probably because all is always the aggregate of its children:

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

One way to deal with 'All' would be to do

FREEZE [All Products];

Before writing assignments on levels below All in Products dimension. This, of course, assumes that there are no other changes which may affect All further in the script.

|||Sweet, thanks!|||Searching through the forums, I have found that I have a use for this as well. The only exception is I need the calculation to work for certain calculated members and not all measures and calculated members. I tried adding another SCOPE statement with my specific measures, but it didn't work as I expected.
Here's an example of the code I have that works for all measures. It would be appreciated if I could get this updated to work for 3 specific measures. thanks

FREEZE [All Products];

SCOPE [1/9/2007] : NULL;

SCOPE Accessory;

Accessory = Accessory + Components;

END SCOPE;

END SCOPE

|||

FREEZE [All Products];

SCOPE ({Measure1, Measure2, Measure3}, [1/9/2007] : NULL);

Accessory = Accessory + Components;

END SCOPE

|||Works perfect. I realized I was trying to use calculated members instead of the measures in which they were derived.

Thanks for the help.

overriding aggregate value of a measure to only total based on the children of the an attributes

Going off of Adventure Works, there's a product attribute:

[Product].[Product].[Product]

and now I also have an old product attribute:

[Product].[Old Product].[Old Product]

(people wanted to see the before and after view of different product assignment).

I also have a estimate of sales for each product and old product. When using just this measure - that amount is around twice as much as before - that's ok, but when using just product or just old product, I just want to see the sum of its children.

How could one define a calculated measure so that if the current level is [Product].[Product].All, then the aggregate should be the sum of the children?

This is sort of how I'd be using it:

select [estimated sales] on 0, [Product].[Product].[Product] on 1

from [Adventure Works]

the all line should be the sum of the measure used for all the children (new behavior) and each product row would have the the estimated sales - so this would stay the same.

thinking about this a little more - the 'All' level would be the curren tlevel if the attribute wasn't even used, so I'm not sure what to do to really depict which product attribute was added. And if a person added both? I'd probably want to just show the product totals.

What I'm going to try for now is add another dimension measure with an attribute defaulted to 1 and in the estimated sales, I'll add a 1 to the new records and a 0 to the old ones. This should address the issue that I have.

Thanks!