Denying access to a whole dimension

In AS2K dimension security I could deny access to a whole dimension using the 'Fully Restricted' setting on a role. In AS2005 I can't see how to do the same thing - the only thing I can do is to deny access to every member on every attribute but even then I still see the dimension name in the metadata. BOL suggests it is possible but its descriptions of the UI must have been written before RTM and don't bear much resemblence to the current situation; I've also had a go at setting some of the properties on the permissions object in the XMLA definition of my cube but they don't work either. Is this feature still available? If not, can we have it back?

Chris

Hi Chris.

My recent experience is we had to go "outside" the native role based functionality to achieve the functionality you defined. Our work is probably a demonstration of my ignorance on how to get the same funtionality we had in AS2K expressed in AS2005. When I say go "outside" I mean we used table based security which married the user with the branch of the organization hierarchy they are allowed to see - and ONLY that branch. The user does not see any other members from the organizational hierarchy. I think you're familiar with the solution. If not, let me know and I'll email you the whitepaper.

If Chris is correct - where we can't get the same functionality as we had in AS2K, then I second his suggestion to "have it back."

PGoldy

|||

Hello Paul. Have you done this in the old way of dynamic security, by a security fact table or a stored procedure. If there is any new written on this subject I would like to know.

Regards

Thomas Ivarsson

|||

Hi Thomas. The security technique was teh "old" way of dynamic security. Nothing new here, except that it works in AS2005. Nothing new written here.

Best Regards,

PGoldy

|||

Hi,

Did you try to design a perspective for the role you want to restrict acces ?

Regards

|||

Thanks for the feedback, everyone. It does indeed look like a forgotten feature - I'll post it in Connect. Alex - yes, my workaround involves using dimension security to restrict all the members on my dimension then use a perspective to hide the dimension itself.

Chris

|||

Thanks Chris and everyone for rasing this issue.

Please go ahead and file the connect case for it.

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

|||Hi,
I was trying to point the same thing out here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=785302&SiteID=1

Can I get somehow access to Connect web site (which afaik was closed for new-comming users), I have some other issues as well, which would be nice to handle in next update. I understand that this forum is not intended to collect any errors and that nobody will enter it into Connect programme.

Thanks,
Radim

|||

Hi Radim,

I have no idea whether Connect is closed to new users - it seems a bit strange if it was. It is a bit of a nightmare to navigate through though.

Anyway, I've submitted this issue here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410

Please vote on it!

Chris

|||

Absolutly!

You've had my vote !

Best, Jocke

|||

Hi PGoldy,

Can you email me the whitepaper on this? i have the same problem right now on security (how i can restrict the users with the amount of data they can see).

thanks,

cherriesh

|||

Hi Cherriesh. Better than a document, here's an on-line link to the information your looking for. The author is Richard Tkachuk who is part of the Analysis Services team at MS - very reliable source. Good luck.

PaulG

http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm

Denying access to a whole dimension

In AS2K dimension security I could deny access to a whole dimension using the 'Fully Restricted' setting on a role. In AS2005 I can't see how to do the same thing - the only thing I can do is to deny access to every member on every attribute but even then I still see the dimension name in the metadata. BOL suggests it is possible but its descriptions of the UI must have been written before RTM and don't bear much resemblence to the current situation; I've also had a go at setting some of the properties on the permissions object in the XMLA definition of my cube but they don't work either. Is this feature still available? If not, can we have it back?

Chris

Hi Chris.

My recent experience is we had to go "outside" the native role based functionality to achieve the functionality you defined. Our work is probably a demonstration of my ignorance on how to get the same funtionality we had in AS2K expressed in AS2005. When I say go "outside" I mean we used table based security which married the user with the branch of the organization hierarchy they are allowed to see - and ONLY that branch. The user does not see any other members from the organizational hierarchy. I think you're familiar with the solution. If not, let me know and I'll email you the whitepaper.

If Chris is correct - where we can't get the same functionality as we had in AS2K, then I second his suggestion to "have it back."

PGoldy

|||

Hello Paul. Have you done this in the old way of dynamic security, by a security fact table or a stored procedure. If there is any new written on this subject I would like to know.

Regards

Thomas Ivarsson

|||

Hi Thomas. The security technique was teh "old" way of dynamic security. Nothing new here, except that it works in AS2005. Nothing new written here.

Best Regards,

PGoldy

|||

Hi,

Did you try to design a perspective for the role you want to restrict acces ?

Regards

|||

Thanks for the feedback, everyone. It does indeed look like a forgotten feature - I'll post it in Connect. Alex - yes, my workaround involves using dimension security to restrict all the members on my dimension then use a perspective to hide the dimension itself.

Chris

|||

Thanks Chris and everyone for rasing this issue.

Please go ahead and file the connect case for it.

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

|||Hi,
I was trying to point the same thing out here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=785302&SiteID=1

Can I get somehow access to Connect web site (which afaik was closed for new-comming users), I have some other issues as well, which would be nice to handle in next update. I understand that this forum is not intended to collect any errors and that nobody will enter it into Connect programme.

Thanks,
Radim|||

Hi Radim,

I have no idea whether Connect is closed to new users - it seems a bit strange if it was. It is a bit of a nightmare to navigate through though.

Anyway, I've submitted this issue here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410

Please vote on it!

Chris

|||

Absolutly!

You've had my vote !

Best, Jocke

|||

Hi PGoldy,

Can you email me the whitepaper on this? i have the same problem right now on security (how i can restrict the users with the amount of data they can see).

thanks,

cherriesh

|||

Hi Cherriesh. Better than a document, here's an on-line link to the information your looking for. The author is Richard Tkachuk who is part of the Analysis Services team at MS - very reliable source. Good luck.

PaulG

http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm

Denying access to a whole dimension

In AS2K dimension security I could deny access to a whole dimension using the 'Fully Restricted' setting on a role. In AS2005 I can't see how to do the same thing - the only thing I can do is to deny access to every member on every attribute but even then I still see the dimension name in the metadata. BOL suggests it is possible but its descriptions of the UI must have been written before RTM and don't bear much resemblence to the current situation; I've also had a go at setting some of the properties on the permissions object in the XMLA definition of my cube but they don't work either. Is this feature still available? If not, can we have it back?

Chris

Hi Chris.

My recent experience is we had to go "outside" the native role based functionality to achieve the functionality you defined. Our work is probably a demonstration of my ignorance on how to get the same funtionality we had in AS2K expressed in AS2005. When I say go "outside" I mean we used table based security which married the user with the branch of the organization hierarchy they are allowed to see - and ONLY that branch. The user does not see any other members from the organizational hierarchy. I think you're familiar with the solution. If not, let me know and I'll email you the whitepaper.

If Chris is correct - where we can't get the same functionality as we had in AS2K, then I second his suggestion to "have it back."

PGoldy

|||

Hello Paul. Have you done this in the old way of dynamic security, by a security fact table or a stored procedure. If there is any new written on this subject I would like to know.

Regards

Thomas Ivarsson

|||

Hi Thomas. The security technique was teh "old" way of dynamic security. Nothing new here, except that it works in AS2005. Nothing new written here.

Best Regards,

PGoldy

|||

Hi,

Did you try to design a perspective for the role you want to restrict acces ?

Regards

|||

Thanks for the feedback, everyone. It does indeed look like a forgotten feature - I'll post it in Connect. Alex - yes, my workaround involves using dimension security to restrict all the members on my dimension then use a perspective to hide the dimension itself.

Chris

|||

Thanks Chris and everyone for rasing this issue.

Please go ahead and file the connect case for it.

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

|||Hi,
I was trying to point the same thing out here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=785302&SiteID=1

Can I get somehow access to Connect web site (which afaik was closed for new-comming users), I have some other issues as well, which would be nice to handle in next update. I understand that this forum is not intended to collect any errors and that nobody will enter it into Connect programme.

Thanks,
Radim|||

Hi Radim,

I have no idea whether Connect is closed to new users - it seems a bit strange if it was. It is a bit of a nightmare to navigate through though.

Anyway, I've submitted this issue here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=233410

Please vote on it!

Chris

|||

Absolutly!

You've had my vote !

Best, Jocke

|||

Hi PGoldy,

Can you email me the whitepaper on this? i have the same problem right now on security (how i can restrict the users with the amount of data they can see).

thanks,

cherriesh

|||

Hi Cherriesh. Better than a document, here's an on-line link to the information your looking for. The author is Richard Tkachuk who is part of the Analysis Services team at MS - very reliable source. Good luck.

PaulG

http://www.sqlserveranalysisservices.com/OLAPPapers/UsingUserNametoControlDataAccessandDefaultMemberinSSAS.htm

deny permissions issues

Hi,
i'd like to deny permissions on system objects to the public role in a
database.
I have tried using the following commands without any success:
DENY SELECT ON SCHEMA::sys TO public
or
DENY SELECT ON sys.sysobjects TO public
Can someone tell me what is wrong in my commands?
Thx,
ChrisHello Chris
I am not sure what is the reason to revoke SELECT on objects in sys schema
because resultsets will be filtered already by permissions. Anyway here is
statement you can use:
REVOKE SELECT ON sys.sysobjects FROM public
Martin Rakhmanov
<christophe.leroquais@.gmail.com> wrote in message
news:1149148183.181843.75560@.h76g2000cwa.googlegroups.com...
> Hi,
> i'd like to deny permissions on system objects to the public role in a
> database.
> I have tried using the following commands without any success:
> DENY SELECT ON SCHEMA::sys TO public
> or
> DENY SELECT ON sys.sysobjects TO public
>
> Can someone tell me what is wrong in my commands?
> Thx,
> Chris
>

Deny Create Database

User is an NT Administrator and has been made a member of Server Role sys
admin.
I'm not in the master database in query analyzer.
When I do this:
Deny Create Database
To User
I get the following message:
CREATE DATABASE PERMISSION CAN ONLY BE GRANTED IN THE MASTER DATABASE
How do I alter the SQL statement to refer to the master database?
Thanks for any help.Run:
use master
before you run the DENY.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:4B307CF8-97A3-41ED-9F3E-ABB130C8C525@.microsoft.com...
User is an NT Administrator and has been made a member of Server Role
sysadmin.
I'm not in the master database in query analyzer.
When I do this:
Deny Create Database
To User
I get the following message:
CREATE DATABASE PERMISSION CAN ONLY BE GRANTED IN THE MASTER DATABASE
How do I alter the SQL statement to refer to the master database?
Thanks for any help.|||Thanks, but trying to mimic a process all in one place, and the analyzer may
not be the best place to do this. I could do it from a test web page but f
amiliarizing myself with Analyzer environment.
In a web environment, I know I would have to connect to the master database
to issue this command.
However, is there an 'automated' way to switch to that database in the Query
Analyzer so that I can put the code
in the same test file rather than having to run another instance of the Anal
yzer just for that? I would rather comment out a few lines that I am not te
sting at that moment rather than switch back and forth from instance to inst
ance.
Is there a command that I can put in the body of the analyzer sql script to
switch? Sorry. Kind of new at this. All of the lines in this test file do
n't have to be run on Master anyway since they are adding users to specific
databases or they are addin
g server roles which can be added from anywhere, so if I used a separate tes
t file, it would have only these two lines in it anyway.
So is there a command to switch databases that I can place in the Analyzer s
cript? In the meantime, I'll go with your answer, and thanks very much for
your prior reply.|||If you are using Query Analyzer to run a script, then the only way to
guarantee that you are in a particular database is to issue a USE command.
So, if you are running a script from within QA, and you want to be sure you
are in master, then ensure you have the following:
use master
go
-- do something
I'm not sure I understand what the issue is. You don't need separate
instances of QA or anything like that.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:459B2889-43EB-4940-9D24-F74F7462ADCC@.microsoft.com...
Thanks, but trying to mimic a process all in one place, and the analyzer may
not be the best place to do this. I could do it from a test web page but
familiarizing myself with Analyzer environment.
In a web environment, I know I would have to connect to the master database
to issue this command.
However, is there an 'automated' way to switch to that database in the Query
Analyzer so that I can put the code
in the same test file rather than having to run another instance of the
Analyzer just for that? I would rather comment out a few lines that I am
not testing at that moment rather than switch back and forth from instance
to instance.
Is there a command that I can put in the body of the analyzer sql script to
switch? Sorry. Kind of new at this. All of the lines in this test file
don't have to be run on Master anyway since they are adding users to
specific databases or they are adding server roles which can be added from
anywhere, so if I used a separate test file, it would have only these two
lines in it anyway.
So is there a command to switch databases that I can place in the Analyzer
script? In the meantime, I'll go with your answer, and thanks very much for
your prior reply.|||I have been using separate instances of QA so I can use sa in one instanc
e to issue permissions and then see the effects of the issued permissions
by having a lower level user run sql commands in the second instance (i.e. s
econd instance logged in as
Low Level Windows user).
Thanks for the use Master go.
I'm sure it will work, and I appreciate it.
Scott|||Works great, but one thing I noticed:
Even though the commands take fine and the syspermission table record cou
nt changes as you would expect (master file), the command will not turn off
create database for a user in the sysadmin role or for a user who is not in
the sysadmin role but who i
s in the db_creator role.
So it doesn't seem to work at all. I know for a db_owner (non-sysadmin) you
have to turn on database creation after the fact, not turn it off, as I am
testing, so it does not apply in that instance.
So I'm trying to find a combination where Deny Create Database actually work
s where it would make sense. It makes sense that you might want to turn it
off for an administrator. Can't get it to work.|||Alternatively, you can send the default database to master.

>--Original Message--
>If you are using Query Analyzer to run a script, then the
only way to
>guarantee that you are in a particular database is to
issue a USE command.
>So, if you are running a script from within QA, and you
want to be sure you
>are in master, then ensure you have the following:
>use master
>go
>-- do something
>I'm not sure I understand what the issue is. You don't
need separate
>instances of QA or anything like that.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Scotty" <scasti1@.cox.net> wrote in message
>news:459B2889-43EB-4940-9D24-F74F7462ADCC@.microsoft.com...
>Thanks, but trying to mimic a process all in one place,
and the analyzer may
>not be the best place to do this. I could do it from a
test web page but
>familiarizing myself with Analyzer environment.
>In a web environment, I know I would have to connect to
the master database
>to issue this command.
>However, is there an 'automated' way to switch to that
database in the Query
>Analyzer so that I can put the code
>in the same test file rather than having to run another
instance of the
>Analyzer just for that? I would rather comment out a few
lines that I am
>not testing at that moment rather than switch back and
forth from instance
>to instance.
>Is there a command that I can put in the body of the
analyzer sql script to
>switch? Sorry. Kind of new at this. All of the lines
in this test file
>don't have to be run on Master anyway since they are
adding users to
>specific databases or they are adding server roles which
can be added from
>anywhere, so if I used a separate test file, it would
have only these two
>lines in it anyway.
>So is there a command to switch databases that I can
place in the Analyzer
>script? In the meantime, I'll go with your answer, and
thanks very much for
>your prior reply.
>|||Looks like you need to re-think your strategy. If you're in db_creator, you
can create a database. If you want to ensure they can't do it - keep them
out. A sysadmin role member can do anything, so a DENY isn't going to work
here. It's best to look at what they really need and give them only those
permissions or roles. That way, you won't need to use DENY.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Scotty" <scasti1@.cox.net> wrote in message
news:3E662297-270F-4F7B-AE8A-D0E9215E9E83@.microsoft.com...
Works great, but one thing I noticed:
Even though the commands take fine and the syspermission table record
count changes as you would expect (master file), the command will not turn
off create database for a user in the sysadmin role or for a user who is not
in the sysadmin role but who is in the db_creator role.
So it doesn't seem to work at all. I know for a db_owner (non-sysadmin) you
have to turn on database creation after the fact, not turn it off, as I am
testing, so it does not apply in that instance.
So I'm trying to find a combination where Deny Create Database actually
works where it would make sense. It makes sense that you might want to turn
it off for an administrator. Can't get it to work.