3 Replies Latest reply on Jan 24, 2013 3:59 PM by TimG

    EIS - SQL Server Account not seeing certain Columns

      Hi All,

      Pretty simple issue, but no apparent reason that we can see:

      When looking at our SQL sever tables via EIS (like you do before creating a new model); if looking at our DEV MS SQL Server we do not see all the columns for various tables that we do see if looking at our LIVE MS SQL server. The user accounts look identical and there doesn't appear to be any differences in the tables or databases being viewed. BUT if we use the MS SQL sever ADMIN account (naughty) we can see evertying in both servers.

      When looking at the tables using teh MS SQL Server studio, all columns can be seen on both DEV and LIVE, its just when you look at the tables in EIS and hence you can't select the columns for your OLAP model.

      Many thanks

        • 1. Re: EIS - SQL Server Account not seeing certain Columns
          Are you really sure the accounts are identical?

          SQL Server supports column-based security, and from the SQL Server Management Studio GUI a SELECT permission to all columns (at least in the version I'm running, 2008) looks the same at first glance to a SELECT permission from only some columns. I'd give instructions on how to test but I don't know your version or whether it'd be the same.

          Anyway, my guess is that there are some column-based restrictions.

          Easy way to check is to use a SQL client and try SELECT * FROM YourTable. Obviously, while logged in via the account you're using in EIS!

          If you don't have access to all columns you will get "The SELECT permission was denied on the column 'YourColumn' of the object 'YourTable'..." etc.
          • 2. Re: EIS - SQL Server Account not seeing certain Columns
            Many thanks for the reply Tim. I hope life is good. How is the "star office" ADD-IN business? Is Oracle still using it for the last remaining remnants of Sun?

            I'm "informed" the accounts are identical.....apparently there is no security on the columns AND I've ran some SQL from the SQL Studio using the same user account and there is no problem pulling data for the columns that it cannot see in EIS. i.e. doing a SELECT on ALL columns returns results fror all columns and no error.

            Now EIS is on window server 2008, our MS SQL 2008 server is on a different server (I'm guessing that too is windows server 2008) - so it could be the way the servers talk to each other?

            I'm guessing that EIS isn't doing anything apare from returning from the SQL server what it can "see" based upon the user account used for accesssing teh MS SQL server. There isn't a "setting" in EIS affecting this?

            • 3. Re: EIS - SQL Server Account not seeing certain Columns
              Other Tim; I think you mean Mr Tow! Although I'm from the UK, so we may also have met - Hyperion is a small world.

              I'm afraid in that case, I'm almost out of ideas. My EIS experience is several years old now, but I don't remember having seen this problem.

              You could try to make a connection using a SQL client on the EIS server (I think even Excel can do this with the External Data options) using the same ODBC connection. That would rule out / identify a driver or environment problem.

              And I assume you've already tried just creating a new model from scratch.

              Last guess, there's nothing obviously 'different' about those columns - a particular datatype or unusual naming - is there?

              Still, the fact that it's account specific suggests it shouldn't be any of those things.