include all the columns, but blank them out based on permissions is what i would recommned.
something like this?
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
akirajt (8/6/2012)
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)
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!