The Owner of SQL Server Database Views

[goofaholix]

I have many reports based on Database Views.

In the Development environment these were created with dbo as the owner. Moving to production they don't want to give me dbo rights, just the ability to create or modify Views, so the owner of the Views has been reset to my user id.

However when I run the reports it comes up with an error.

I have deleted the Views from the Catalog and reloaded them, setting them qualified less.

I can run the View no problem in SQL Server Enterprise Manager.

I have also tried to create new Reports based on these Views, and get the same error, whereas if I create a new report on a View that belongs to dbo I have no problem.

Does a View need to belong to dbo to work with Impromptu? Or is there something else that could solve the problem?

Thanks.

 

[goofaholix]

Another test I've done is create a new View using my User id as the owner, same problem.

 

[griffindm]

goof,

It sounds like you have what is called a "schema leveling" issue that is known to affect Impromptu on MSSQL Server.

Try this:

Steps:
1. Make a copy of your catalog.
2. Type the entry "Schema Leveling Mode=1" in the [Startup Options] section of the Impromptu.ini file (located in the C:\Program Files\cerx\bin folder).
3. Open Impromptu Administrator
4. From the Catalog menu, click Open.
5. In the Catalog Logon dialog box, click OK.
6. From the Catalog menu, click Tables.
7. Remove the dbo level between the database name and the table name.
8. Click OK and close the catalog.
9. Close Impromptu.
10. Remove the Scheme Leveling Mode entry from the Impromptu.ini file.
11. Restart Impromptu.


Hope this helps.

Dave Griffin



The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[goofaholix]

I've tried it, it hasn't solved the problem.

The first attempt I also deleted the red database level and after that I couldn't add any new tables.

The second time just the blue owner level, and the problem persists.

 

[griffindm]

goof,

From your description of what you did, I would try to rename the dbo level to your database id that now owns the views. Let me know if that allows you to change and add views and to access current views.

Dave G.


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[goofaholix]

One of the first things I tried was deleting the Views from the Catalog and re-adding them, with the new database owner being named on that Catalog level automatically, to no avail.

That effectively does the renaming that you are suggesting, doesn't it?

 

[tskelton]

goofaholix,

If I'm understanding your problem correctly, you need to take one more step.

First, start with a fresh copy of your original catalog. Then delete the folders from the catalog:
Catalog...Folders, then click on the Cut button for each folder.

Now delete the views from the catalog and re-add them. Keep in mind that you will have to re-establish your joins if you do this. Renaming the dbo level, as Dave suggests, avoids the loss of the joins.

Re-adding the views will automatically re-create the folders that you deleted, with the original folder names.

This is the process I use when I'm in a similar situation. Again, I may not be understanding your problem correctly. Let me know if I've misunderstood.

-Todd

 

[goofaholix]

I believe that is exactly what I've done.

Except that I haven't renamed the dbo level, I've kept the name that came through from the database which is the new user id which is the owner of the Views. No joins have been setup in the Catalog for the Views as each select stands alone, so I take it there is no need to rename it back to dbo in the Catalog?

And I've also tried removing the database owner level from the Catalog.

 

[goofaholix]

Further to the above.

I tried doing a select on the View in Excel using MS Quesry, it worked, this provers to me the issue is with Impromptu, not the database or the Operating System.

I then created a brand new Catalog and generated all the tables and Views afresh, it also worked, I could report of Views I couldn't access before.

So this preoves to me the problem is within my Catalog, but ditching the Catalog and rewscreating it is not an option, because while the Views have no joins the 500+ tables do and I can't recreate those.

I'll keep digging.