InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 01:05:20 GMT20http://www.sqlservercentral.com/Forums/Topic1340374-363-1.aspxI can think of several ways to do this.The first is to reflect the design into SSAS, and let the cube handle the security. While I'm only an SSAS novice, this is one of the things that it does well from memory. Users should only see what they are permitted to see.The second is to create schemas for the users and in each schema, create views that filter out the data they are not permitted to see. Grant select rights on the views, not on the underlying tables.The third is a variation on the second, and I think is better in terms of management. That is to create "subject areas" (again I would recommend schemas), create the views within the subject areas, assign the necessary permissions to the subject areas, and finally assign the users to the subject areas.Wed, 21 Nov 2012 12:10:48 GMTBruce W Cassidyhttp://www.sqlservercentral.com/Forums/Topic1340374-363-1.aspxWell. Remember that the sql security model is deny by default;Its easy to forget that as a developer since you test as sa/sysadmin.That means you can create a view and grant SELECT permissions to Bob, and he has no access at all to the table(s) the view uses.But you are correct, if he had permissions to the table he could query it diectly.The solution using ISMEMBER would require some tighter permissionsThu, 27 Sep 2012 04:08:09 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1340374-363-1.aspxSorry in advance for my novice question, but how will a CASE WHEN ISMEMBER() THEN clause be useful if the user is tasked with writing his own query in the first place? Couldn't he/she simply use a SELECT * to circumvent this? Or are you inserting this into a stored procedure and limiting the users to only being able to run the stored procedures? Again, apologies for the novice question.Wed, 26 Sep 2012 19:50:29 GMTdj1202http://www.sqlservercentral.com/Forums/Topic1340374-363-1.aspxahh, but with column level permissions, you get errors that says user does not have permission to object:column, instead of empty values.it depends on what the OP wants in that case.For me, the column level permossions eliminate the ability to use the same report for all users...you need something that changes the report based on the user in that case.if security is via database permissions, i would use the IS_MEMBER('RoleName') = 1 function in the case statement example above, filter visibility based on the role the calling user is in;if he has his own table within the application wwhich determines who is in which role, it's a basic modification of the same principal.Tue, 07 Aug 2012 05:39:47 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1340374-363-1.aspxHere is a link on column level permissionshttp://www.mssqltips.com/sqlservertip/2124/filtering-sql-server-columns-using-column-level-permissions/This assumes that your users log dierctly into SQL Server to run their queries. If you have some kind presentation layer that might be a better place to implement this.Tue, 07 Aug 2012 05:15:18 GMTnick.mcdermaidhttp://www.sqlservercentral.com/Forums/Topic1340374-363-1.aspxinclude all the columns, but blank them out based on permissions is what i would recommned.something like this?[code]SELECT SomeColumns, CASE WHEN IsSalesman = 1 THEN null ELSE TaxAmount END AS TaxAmount, CASE WHEN IsSalesman = 1 THEN null ELSE TotalAmount END AS TotalAmount FROM factTransaction[/code][quote][b]akirajt (8/6/2012)[/b][hr]If I have three different user with different occupation (manager, salesman, accounting)The main question is to how display right column to right person based on star schema and requirement below in SQL server?The fact and dim are using regular table inside of data mart.Background information: The manager is authorized to see all column in factTransaction The salesman is not allowed to see TaxAmount, TotalAmount and ProductBusinessKey. The Accounting is note allowed to see Product Quantity, ProductPrice and GeographyFullname.In windows, the they have their own user account.The picture is take from the address (http://stackoverflow.com/questions/3308647/design-of-a-data-warehouse-with-more-than-one-fact-tables)[img]http://i.stack.imgur.com/DlnsI.png[/img][/quote]Mon, 06 Aug 2012 07:39:11 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1340374-363-1.aspxIf I have three different user with different occupation (manager, salesman, accounting)The main question is to how display right column to right person based on star schema and requirement below in SQL server?The fact and dim are using regular table inside of data mart.Background information: The manager is authorized to see all column in factTransaction The salesman is not allowed to see TaxAmount, TotalAmount and ProductBusinessKey. The Accounting is note allowed to see Product Quantity, ProductPrice and GeographyFullname.In windows, the they have their own user account.The picture is take from the address (http://stackoverflow.com/questions/3308647/design-of-a-data-warehouse-with-more-than-one-fact-tables)[img]http://i.stack.imgur.com/DlnsI.png[/img]Mon, 06 Aug 2012 00:42:46 GMTakirajt