Strange problem with using sa account and Crystal reports

Question

Strange problem with using sa account and Crystal reports

William Hall Dillon-327038

SSC Eights!

Points: 990
More actions
December 12, 2006 at 10:50 am

#116522

Hi All
This is pretty strange. Using Crystal reports with the ODBC setup as using the 'sa' account, I can see the tables but not the views. If I change my ODBC to use a database user, I can see the tables and views
What's up with that??
Thanks
William

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 1

The most likely answer is the views were created by the database user and when the user created the views, he/she didn't prefix the names with dbo.

When you don't qualify your object names, I belive it looks for current-user owned objects, then dbo owned objects. SA only looks for objects owned by dbo.

Recreate your views and when you are doing that, prefix the name with DBO.

A good habit is to pretty much always qualify your object names.

SQL guy and Houston Magician

William Hall Dillon-327038 SSC Eights! Points: 990 More actions · Answer 2

I checked the views and they are owned by dbo.

I still can't figure this one out

Thanks

William

R Michael SSCarpal Tunnel Points: 4211 More actions · Answer 3

Hmm, that is a mystery. When you are referencing these views, what happens if you fully qualify the names (Databse.owner.objectname)?

Does it still work with one user but not the other?

SQL guy and Houston Magician

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Try creating a view while logged in as SA then see what happens. But beyond that, you are not really going to be using SA for you Crystal Reports report are you?

MattieNH SSCertifiable Points: 7463 More actions · Answer 5

I'm not sure what version of Crystal you're using, but if I recall correctly for version XI, I had to set up the Crystal environment to show certain object types in a database.

Open Crystal, then select File\Options and go to the database tab. Make sure all the object types you want to see are selected.

Mattie

William Hall Dillon-327038 SSC Eights! Points: 990 More actions · Answer 6

This is just plain odd - we won't be using sa for the ODBC anyway. We are using Crystal 10 and one of the developers noticed this.

I even went to File\Options to make sure that the "view" was check in the database tab

Go figure

William

Antares686 SSC Guru Points: 125444 More actions · Answer 7

Well I woul still try creating a view while logged into the SQL Server as SA. But I would change the SA password personally since you won't be using in the ODBC anyway. Then remind the developer to use the proper account only. May be odd but it shows you somthing even odder and that is the developers willingness to use the SA account instead of the intended account IMHO

William Hall Dillon-327038 SSC Eights! Points: 990 More actions · Answer 8

Yup, I'll give that a try in a while. Generally the developers don't have access to sa.

I'll let the thread know what happens when I try this.

William