how could i hide my finacial data to sa when i store them in sql server 2k?

what we store in db is informatin about company finacial status.
we don't want anyone in charge of the server can read the those data.
what can i do without 3rd party software like activecrypt?
thank you.You can't prevent a member of the sysadmin role in SQL Server (like sa) from
viewing data so you would need to encrypt it either before storing it in SQL
or use a third party product
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"xuyan" <microsoft.net@.sina.com> wrote in message
news:eRfUysosFHA.3404@.TK2MSFTNGP09.phx.gbl...
> what we store in db is informatin about company finacial status.
> we don't want anyone in charge of the server can read the those data.
> what can i do without 3rd party software like activecrypt?
> thank you.
>|||Hi
Where does the info reside? One or many tables?
You can adde the user to db_denydatareader role and make sure that he/she is
not a member of sysadmin server role.
"xuyan" <microsoft.net@.sina.com> wrote in message
news:eRfUysosFHA.3404@.TK2MSFTNGP09.phx.gbl...
> what we store in db is informatin about company finacial status.
> we don't want anyone in charge of the server can read the those data.
> what can i do without 3rd party software like activecrypt?
> thank you.
>

How Change field value triggered by date?

In a table i had field called Status and it is of type integer and another two field From and To ... they are of type date .....
what i want to do is to change the value of status field when the current date become equal to the From field and also make another change when the current date become equal to the To field ...

So How can i do This on SQL server 2005?

You need to run a query to update the rows, something like this

UPDATE mytable
SET Status = CASE
WHEN datediff(d, From, getdate()) = 0 THEN 1
WHEN datediff(d, To, getdate()) = 0 THEN 2
END

You could create a calculated column based on the same expression if you want status to always be up to date without running the query first. Otherwise schedule a job to run that query every night so that the status column is always correct.

|||

Thank You for your reply and it is helpful,

But what if the From and To fields in a child table to the original table that have the field status?

e.x. Master Table have the following fields:

PersonID, Name, Status

Details Table has the following fields:

ID,PersonID (as foreign key to the master table), From, To

How the query will look like?!

|||

UPDATE mastertable
SET Status = CASE
WHEN datediff(d, detailtable.From, getdate()) = 0 THEN 1
WHEN datediff(d, detailtable.To, getdate()) = 0 THEN 2
END
FROM mastertable
INNER JOIN detailtable ON mastertable.PersonID = detailtable.PersonID

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?

How can we determine status of defined aggregates

We have a large Analysis Services 2005 installation with many partitions.

Is there an XMLA command or MDX query that will list all of the defined aggregations and their current status? We would like to run this command after an incremental update to make sure that we have not had an inadvertant dropping of aggregates.

Thanks,

Marty

I think you want the DISCOVER_PARTITION_STAT command. It should return a list of the aggs that are processed. If the list it returns is missing any, then you know

Code Snippet

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_PARTITION_STAT</RequestType>

<Restrictions>

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">

<DATABASE_NAME>Adventure Works DW</DATABASE_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

<MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>

</RestrictionList>

</Restrictions>

<Properties>

</Properties>

</Discover>

That's a bit difficult to read because it returns XML, so I'd suggest you check out a stored proc Darren Gosbell wrote at http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=XmlaDiscover

It returns a nice table which is easier to read. The equivalent command would be:

Code Snippet

CALL ASSP.Discover("DISCOVER_PARTITION_STAT","<DATABASE_NAME>Adventure Works DW</DATABASE_NAME><CUBE_NAME>Adventure Works</CUBE_NAME><MEASURE_GROUP_NAME>Internet Sales</MEASURE_GROUP_NAME><PARTITION_NAME>Internet_Sales_2003</PARTITION_NAME>")

If anyone else has other suggestions for better ways to quickly identify which aggs aren't processed, I'd like to hear it.

|||This worked great. Thanks.|||

We have a large number of aggregates that are showing a size of 0. What exactly does that mean? I've been unable to find any reference to aggregation_size.

<row>

<DATABASE_NAME>Phase 1</DATABASE_NAME>

<CUBE_NAME>Sales</CUBE_NAME>

<MEASURE_GROUP_NAME>Sales</MEASURE_GROUP_NAME>

<PARTITION_NAME>S200511</PARTITION_NAME>

<AGGREGATION_NAME>Aggregation 54</AGGREGATION_NAME>

<AGGREGATION_SIZE>0</AGGREGATION_SIZE>

</row>

Thanks,

Marty

|||

AGGREGATION_SIZE means the number of rows in that agg. For instance, if you have a partition for 2006 that has 1000 rows in the fact table then the XMLA query I mentioned above will give show you one <row> tag with no AGGREGATION_NAME showing AGGREGATION_SIZE of 1000. Then if you have a simple agg built on calendar month and nothing else, you should see another <row> showing an AGGREGATION_SIZE of 12 (meaning there are 12 rows in that agg, one for each month). Make sense?

Because you're seeing sizes of 0, I assume that means that there are no rows in your fact table for that partition. Can you confirm this?