Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

FILTER( [DIMUSERS].[DIMUSERS].Members,

'POLARIS\'+ [DIMUSERS].[DIMUSERS].Currentmember.Name=username)

When After execuing the above MDX Its doing hardcode as the login of the Current User

which is not affecting for other Users.

So that Who ever user login to the system everybody getting all the User deatils

This is Urgent plz help me in this regards

Thank U

Check that your users are not setup in the Administrators group, or are not part of a windows group that is in the admin role. This is definitely true for the server admins and possibly also for database admins, but I have not double checked that.|||

Thanks Daren

what you gav solution I did the same still its comming the same

|||

I'm not entirely sure I understand your issue. Are you possibly saying that the members are restricted at the lower level, but the parent levels still have the total for all members? If this is your problem, go into the advanced tab and turn on the "Visual Totals" option. This will make the value for all the parent members only show a total which is the sum of the values to which the user has access.

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

I want the cube data to be filtered on the basis of the current domain user. Is there any way to get that filter implemented on analysis cube or through MDX query as said above.....please its urgent...

|||You can either do what the first poster was doing and setup a "users" dimension and setup the allowed set in your role to filter using the username function. or you can setup a series of different roles with different allowed sets and allocate users to the different roles - it really depends on your specific security needs as to which is the better approach.

Dynamic Security to Cube

I have the requiremrnt that when a User loggs in He would be able to see only his and his Ancestores Details(means he ll be able to see the users deatils those are reporting him)

The MDX Query i wrote in Roles--> Dimension Data-->Advanced-->Allowed Memberset is.

FILTER( [DIMUSERS].[DIMUSERS].Members,

'POLARIS\'+ [DIMUSERS].[DIMUSERS].Currentmember.Name=username)

When After execuing the above MDX Its doing hardcode as the login of the Current User

which is not affecting for other Users.

So that Who ever user login to the system everybody getting all the User deatils

This is Urgent plz help me in this regards

Thank U

Check that your users are not setup in the Administrators group, or are not part of a windows group that is in the admin role. This is definitely true for the server admins and possibly also for database admins, but I have not double checked that.|||

Thanks Daren

what you gav solution I did the same still its comming the same

|||

I'm not entirely sure I understand your issue. Are you possibly saying that the members are restricted at the lower level, but the parent levels still have the total for all members? If this is your problem, go into the advanced tab and turn on the "Visual Totals" option. This will make the value for all the parent members only show a total which is the sum of the values to which the user has access.

If this is not your issue maybe an example of what you are seeing and what you would like to see would help.

|||

I want the cube data to be filtered on the basis of the current domain user. Is there any way to get that filter implemented on analysis cube or through MDX query as said above.....please its urgent...

|||You can either do what the first poster was doing and setup a "users" dimension and setup the allowed set in your role to filter using the username function. or you can setup a series of different roles with different allowed sets and allocate users to the different roles - it really depends on your specific security needs as to which is the better approach.

Dynamic query

Hello

I'm a newbie in sql server. anyone can explain what does Dynamic query means?

thanks.

Sure, but for some basic understandings (perhaps your questions will be cleared after the detailed the explanation of this text) look for:

http://www.sommarskog.se/dynamic_sql.html

HTH, jens Suessmeyer.

|||On Sat, 21 Jan 2006 11:30:01 -0800,

wrote:

>I'm a newbie in sql server. anyone can explain what does Dynamic query

>means?

Hi Dato0011,

Dynamic SQL means that the query is not precoded in a stored procedure

or application, but created on the spot. Example of non-dynamic SQL:

SELECT COUNT(*) FROM pubs..authors

Example of dynamic SQL:

DECLARE @.SQL nvarchar(400)

SET @.SQL = 'SELECT COUNT(*) FROM pubs..authors'

EXECUTE (@.SQL)

(In a real situation, part of the dynamic SQL would be based on user

input).

Dynamic SQL can be advantegeous in some situations, but it is also

extremely dangerous. Using dynamic SQL requires permissions for the end

users on your tables, and -the most important danger!- exposes your DB

to the danger of SQL injection.

For a completediscussion of the curse and blessing of dynamic SQL, see

Erland Sommarskog's article:

http://www.sommarskog.se/dynamic_sql.html

--

Hugo Kornelis, SQL Server MVP|||

Hi Dato0011,

It seems that some weird bug deleted the formatting of my reply. Let's do that one more time, shall we?

On Sat, 21 Jan 2006 11:30:01 -0800, <Dato0011@.discussions.microsoft.com>
wrote:

>I'm a newbie in sql server. anyone can explain what does Dynamic query
>means?

Hi Dato0011,

Dynamic SQL means that the query is not precoded in a stored procedure
or application, but created on the spot. Example of non-dynamic SQL:

SELECT COUNT(*) FROM pubs..authors

Example of dynamic SQL:

DECLARE @.SQL nvarchar(400)
SET @.SQL = 'SELECT COUNT(*) FROM pubs..authors'
EXECUTE (@.SQL)

(In a real situation, part of the dynamic SQL would be based on user
input).

Dynamic SQL can be advantegeous in some situations, but it is also
extremely dangerous. Using dynamic SQL requires permissions for the end
users on your tables, and -the most important danger!- exposes your DB
to the danger of SQL injection.

For a completediscussion of the curse and blessing of dynamic SQL, see
Erland Sommarskog's article:
http://www.sommarskog.se/dynamic_sql.html

--
Hugo Kornelis, SQL Server MVP

Dynamic Connection String

Hi, Can I pass the connection string dynamically. That means the user will specify the database, he/she wants to connects to and user name and password for the same and display and list of Models/Reports stored in that database.Yes, you can. After all the ConnectionString is just a string. So before you make an SqlConnection, you build up the ConnectionString.

string userName = "Joe";
string password = "****";
string server = "MyDatabaseServer";
string connectionString="user="+userName+";password="+password etc etc

Of course this is a ugly fasttype example. You can beautify this using StringBuilders etc.|||Thanks. But, I wanted to know how this can be passed dynamically when the user runs the report from the report server. Which means, the report is already designed and published on the report server. Firstly, can a report/report model be designed on dynamic connection string.|||I think the question was about dynamic connection strings in Reporting Services. This is a feature of SQL Server 2005 Reporting Services. There are a couple of workarounds for SQL 2000 RS (stored procs, cross database queries, etc.).