problem with Roles (Read Definition)

Hi everybody,

I have a problem:

- if I assign to a role the general "Read Definition" rights (on the database), I am not able to reduce the visibility on the dimensions, since they are all with "Read Definition" rights and at least "Read" access.

- on the contrary, if I dont'assign to a role the general "Read Definition" rights, and then set "Read Definition" rights on some dimensions (and the "Read" access, too), the users of this role are not allowed to connect to the OLAP database!

This way, it's not possible to assign selective visibility to the dimensions, and it seems you are forced to always set the general "Read Definition" rights in order to access a database (so this flag would be quite unuseful). Can someone suggest me what I am doing not correct?

Thank you very much,

ReadDefinition permission allows a user bit more than access to dimensions , It allows user to retreive metadata for dimension.

What you probably looking for is to grant specific role Read access to one of your cubes. For instance grant Read access to your Sales cube.

If you would like to deny users seeing data in one of dimensions, you can click on the Dimension Data node in the Edit Role dialog.
In this page you should be able to deny acess to individual attributes in your dimension by selecting "Deselect All members" option. Go through all attributes in your dimension and Role users will not be able to see dimension data.

You can test security the Role you create by going into the Cube browser and clicking on the "Change User" (first icon on the upper left corner of the browser).

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thank you very much for your kind answer.

From what you say, I understand there is no way to limit access to OLAP database metadata to users in roles with general Read Definition rights, correct? Therefore, all the users are allowed to see the definition and properties of all the dimensions, cubes, etc and the roles themselves and we can limit only the "data" (members, cells and so on). Is it?

Another question, in the BIDS environment I couldn't find the "ApplyDenied" property for the Dimension Data, can it be used only programmatically or maybe I didn't search in the right place?

Thank you very much!

|||

To clarify my post above;

To grant Read access to your cube and dimension you don't need to grant your users ReadDefinition rights to your database. It is just enough to grant them Read rights to your cubes and configure access to dimensions.

ReadDefinition is "stronger" compared to the Read permission. You would grant ReadDefinition right to allow your users to peroform advanced tasks: like creating local cubes...

As for the properties of the objects avaliable to you through AMO. Try and browse your database using AmoBrowser sample application. If you installed samples, you should find it under %Drive%:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO. Using AmoBrowser you should be able to see all properties exposed for any AS object.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Problem with role permissions - supplemental info

I foud a way to fix this, but it's a long process (this DB is huged), and I
would still like to know why this hapened. Here is what I did.
To fix a database (refered as orgDB below) with a role permissions problem
(won't save), do the following
a.. Create a new database (refered as tmpDB below)
b.. Import all tables from orgDB into tmpDB
c.. in tmpDB, run scrip to add users and set permissions
d.. Detach both database (right-click on db name, All Tasks, Detach
database). The DBs will disapear from the list.
e.. In Windows explorer, rename the original data (mdf) and log (ldf)
files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
orgDB.ldf
g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
Database and follow the wizzard and select the orgDB.mdf file.
If there is a simplier (shorter) way to do this, I would like to know it.
C.R.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...

quote:

---

> We are experiencing a problems setting permissions on roles on SQL Server
> 2000. Our SQL server is holding several databases (60+) and on some of

---

them

quote:

---

> (not all), when we change permissions on tables for a role (Public for
> example), they do not seam to be saved.We tried through Enterprise Manager
> and SQL Query Analyser with the same result. We change the permission,
> click on OK, go out of EM and when we go back in, the permissions are back
> the way they were before we changed them What is puzzling us is that it
> works fine on some databases and not on other. We do not know of any
> database options that would prevent changing the permissions, is there

---

one?

quote:

---

> Anyone as any taught about this?
> MS SQL Server 2000 (sp3a)
> Windows 2000 (sp4)
> --
> C.R.
>

---

I've never heard of something like this before - it'd be great if you could
call product support and help them reproduce this so we can see if there's a
problem with SQL Server or something really really strange about the
database...
-Richard
Richard Waymire, MCSE, MCDBA
This posting is provided "AS IS" with no warranties, and confers no rights.
"C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I foud a way to fix this, but it's a long process (this DB is huged), and

---

I

quote:

---

> would still like to know why this hapened. Here is what I did.
> To fix a database (refered as orgDB below) with a role permissions problem
> (won't save), do the following
> a.. Create a new database (refered as tmpDB below)
> b.. Import all tables from orgDB into tmpDB
> c.. in tmpDB, run scrip to add users and set permissions
> d.. Detach both database (right-click on db name, All Tasks, Detach
> database). The DBs will disapear from the list.
> e.. In Windows explorer, rename the original data (mdf) and log (ldf)
> files: orgDB.mfd >> xx_orgDB.mdf and orgDB.lfd >> xx_orgDB.ldf
> f.. In Windows explorer, rename the temporary data (mdf) and log (ldf)
> filesto the original name: tmpDB.mfd >> orgDB.mdf and tmpDB.lfd >>
> orgDB.ldf
> g.. In Enterprise Manager, right-click on Databases / All Tasks / Attach
> Database and follow the wizzard and select the orgDB.mdf file.
> If there is a simplier (shorter) way to do this, I would like to know it.
>
> C.R.
>
> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:eYGmqGBwDHA.1744@.TK2MSFTNGP12.phx.gbl...
Server[QUOTE]
> them
Manager[QUOTE]
back[QUOTE]
> one?
>

---

|||I have noted this too.
We have a SQL 2000 SP3 that is having databases of its own.
We recently upgraded (other) databases from a SQL 7.0 server to this s2k
server.
The problem of not saving the permisssions is happening only on the upgraded
dbs not the ones created in SQL 7.0.
WE have managed to replicate this problem on at least 3 different ugpraded
servers.
Although it seems that databases backed up in sQL 7.0 and restore in SQL
2000 does not have this problem but databases upgraded using the copy db
wizards - all have the problem.
Interestingly, checkint the tables (syspermissions) indicate that the role
does actually have the permissions, but they are not shown on EM or scripted
out of the objects if u script them.
Hope that helps in replication of the problem.
MHJ
"Richard Waymire [MSFT]" <rwaymi@.online.microsoft.com> wrote in message
news:eFRavfEwDHA.2492@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I've never heard of something like this before - it'd be great if you

---

could

quote:

---

> call product support and help them reproduce this so we can see if there's

---

a

quote:

---

> problem with SQL Server or something really really strange about the
> database...
> -Richard
> --
> Richard Waymire, MCSE, MCDBA
> This posting is provided "AS IS" with no warranties, and confers no

---

rights.

quote:

---

> "C.R." <Cmdr_W_Riker@.HotMail.com> wrote in message
> news:e3vpYRDwDHA.1872@.TK2MSFTNGP09.phx.gbl...
and[QUOTE]
> I
problem[QUOTE]
Attach[QUOTE]
it.[QUOTE]
> Server
of[QUOTE]
> Manager
permission,[QUOTE]
> back
it[QUOTE]
>

---

Problem with role permissions

We are experiencing a problems setting permissions on roles on SQL Server
2000. Our SQL server is holding several databases (60+) and on some of them
(not all), when we change permissions on tables for a role (Public for
example), they do not seam to be saved.We tried through Enterprise Manager
and SQL Query Analyser with the same result. We change the permission,
click on OK, go out of EM and when we go back in, the permissions are back
the way they were before we changed them What is puzzling us is that it
works fine on some databases and not on other. We do not know of any
database options that would prevent changing the permissions, is there one?
Anyone as any taught about this?
MS SQL Server 2000 (sp3a)
Windows 2000 (sp4)
C.R.See Books onLine:
Resolving Permission Conflicts
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80
The public role is a special database role to which every database user
belongs. The public role:
Captures all default permissions for users in a database.
Cannot have users, groups, or roles assigned to it because they belong to
the role by default.
Is contained in every database, including master, msdb, tempdb, model, and
all user databases.
Cannot be dropped
To protect against unauthorized data access, minimize the permissions
granted to the public role. Instead, grant permissions to other database
roles and to user accounts associated with logins.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
You mis-understood my email. I mentioned the Public role as an EXAMPLE. We
are not trying to drop the public role, or any other role. We are trying to
set the premissions for the role(s). It is happening for ANY role we
created and the public role. We are trying to revoke all rights to the role
public and grant access to some other role(s). The changed permissions are
not saved by the server, weither we use EM or Query Analyzer to do this.
C.R.
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:WrNRXxCwDHA.1868@.cpmsftngxa07.phx.gbl...

quote:

---

> See Books onLine:
> Resolving Permission Conflicts
> mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80
> The public role is a special database role to which every database user
> belongs. The public role:
> Captures all default permissions for users in a database.
> Cannot have users, groups, or roles assigned to it because they belong to
> the role by default.
> Is contained in every database, including master, msdb, tempdb, model, and
> all user databases.
> Cannot be dropped
> To protect against unauthorized data access, minimize the permissions
> granted to the public role. Instead, grant permissions to other database
> roles and to user accounts associated with logins.
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

---

|||from previous thread
" We are trying to set the premissions for the role(s). It is happening
for ANY role we
created and the public role. We are trying to revoke all rights to the
role public and grant access to some other role(s). The changed
permissions are not saved by the server, weither we use EM or Query
Analyzer to do this."
I would run profiler to capture this. Create a sample database, list out
the tsql commands and trace it using Profiler.
As Richard mentioned, if you have a repro of this, open up a case with us
and provide us the details.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||We encounter the same problem like C.R. (with SQL200 sp3)
"We change the permission, click on OK, go out of EM and when we go back =
in, the
permissions are back the way they were before we changed them"
Danny
"C.R." a =E9crit :

quote:

---

> We are experiencing a problems setting permissions on roles on SQL Serv=

---

er

quote:

---

> 2000. Our SQL server is holding several databases (60+) and on some of=

---

them

quote:

---

> (not all), when we change permissions on tables for a role (Public for
> example), they do not seam to be saved.We tried through Enterprise Mana=

---

ger

quote:

---

> and SQL Query Analyser with the same result. We change the permission,=

---

quote:

---

> click on OK, go out of EM and when we go back in, the permissions are b=

---

ack

quote:

---

> the way they were before we changed them What is puzzling us is that i=

---

t

quote:

---

> works fine on some databases and not on other. We do not know of any
> database options that would prevent changing the permissions, is there =

---

one?

quote:

---

> Anyone as any taught about this?
> MS SQL Server 2000 (sp3a)
> Windows 2000 (sp4)
> --
> C.R.

---

|||I made a mistake
with SQL200 sp3a
Danny
Danny Presse a =E9crit :

quote:

---

> We encounter the same problem like C.R. (with SQL200 sp3)
> "We change the permission, click on OK, go out of EM and when we go bac=

---

k in, the[QUOTE]
> permissions are back the way they were before we changed them"
> Danny
> "C.R." a =E9crit :
>
rver[QUOTE]
of them[QUOTE]
r[QUOTE]
nager[QUOTE]
n,[QUOTE]
back[QUOTE]
it[QUOTE]
[QUOTE]
e one?[QUOTE]

problem with role distribution

hello to everybody.

well i have a problem with role distribution too users in a local network. well i crated the roles (browsers) and when they acess report manager they can see only the reports. but when they try to run the report they get a message that either thei don't have access to the analysis services database or the database does not exist. when i ppublish a report isn't there a datasource view with it? then why they cannot run the reports?

thnxs

Hi,

do you defined a datasource in the report properties?

|||do u mean in the report manager? yes when i enter as an administrator and and i look in the properties of a report in the report manager i see the data source of the report. i have chosen integrated security of windows.|||So i am not able to help you.