Letters, October 2002

Editor's Note: SQL Server Magazine welcomes feedback about the magazine. Send your comments to [email protected]. Please include your full name, email address, and daytime phone number with your letter. We edit all letters and replies for style, length, and clarity.

Ownership Chains and NT Logins

I really appreciated Dan Guzman's article "Security Through Ownership Chains" (July 2002, InstantDoc ID 25145), which opened my eyes to several techniques I wasn't aware of. After reading the article, I realized I was making my security implementation more difficult than it had to be. I plan on redoing all my security to employ ownership chains. In the article, Guzman talks about using ownership chains with SQL Server logins. Do the same principles apply when you're using Windows NT User or Group logins instead of SQL Server logins?

—Scott Fox
[email protected]

I'm glad you found the explanation of ownership chains useful. Yes, you can use ownership chains with either Windows or standard SQL Server security. An object owner can be a Windows User or Group account or a SQL Server user or role. Regardless of the account type, the ownership chain remains unbroken as long as the security identification number (SID) of the referencing and referenced object owners is the same. The authentication mode of the user accessing the database doesn't affect ownership-chain behavior.

—Dan Guzman

Recreating a Fact Table from Cube Data

I regularly read Russ Whitney's Mastering Analysis column to learn more about working with Analysis Services, and I have a related question. Recently, I lost the fact table that supports the data in my Analysis Services cube, and I wonder if I can extract data from the cube to recreate the fact table. My cube, called Main, has nine dimensions (Account, Category, Channel, Entity, Func, Organization, Time, Type, and Measures), and each dimension has different levels. I'm trying to build a query that extracts the data from the cube in a tabular fashion or into a text file so that I can recreate my fact table.

For each dimension, I need to find all the leaf-level members that have a data intersection, then write that intersection to a text file. For instance, if the order of the data load is Account, Category, Channel, Entity, Func, Organization, Time, Type, and Measures, the corresponding data intersection would be

"4508", "ADJ", "NoChannel", "C_Chicago","F9000", "No_Org", "2002.JAN", "Non_Ppd", $45,000

How do I generate this type of row output to recreate my fact table?

—Martino Rivaplata
[email protected]

Thanks for your interesting question. I would try to open a relational connection to the Analysis Services cube, then use SQL to find this data for recreating your fact table. A simple statement such as

SELECT * FROM MAIN

will likely generate the results you need because the OLE DB driver for Analysis Services supports SQL access against a cube's data. I don't believe the data source needs to be available for the driver to answer SQL queries. You can experiment with this approach in Microsoft Access or Excel. Good luck.

—Russ Whitney