Login to SQL Server

[kbieber]

This problem started after an upgrade from SQL7 to 2000 and Crystal 7 to 8.5 done concurrently. I was able to convert all existing v7 rpts to v8.5 without a problem and they continue to run OK. The problem is trying to create new reports - when trying to login to the database with the SQL Server data connection, only 1 of the 3 databases shows all of the tables, the other 2 allow the logon, but only display a couple of system tables. I can manually allow access table by table, but obviously this is not the preferred solution. Logging on via ODBC works for all 3 dbs. The user name was created with the same attributes across all 3 db's. Any thoughts on what is preventing the SQL Server login from seeing all of the tables?

 

[synapsevampire]

What does " I can manually allow access table by table, but obviously this is not the preferred solution." mean?

How do you manually do this?

It sounds as if the login doesn't have the proper grants, or you have some table filtering in place within Crystal (seems unlikely).

What can you see from within Query Analyzer with the same login?

What system tables are you trying to get access to?

-k

 

[kbieber]

From within SQL Enterprise Manager, I can allow access to tables one by one. I can see all tables from all db's from Query Analyzer. This is only from Crystal using the SQL Server data access where I can't see the data tables in 2 of the 3 dbs. The user was set up globally in SQL Enterprise Manager with datareader rights.

 

[synapsevampire]

If you're using the same login then you should see the same tables.

And you can grant permission to all tables using the sp_MSreachfortable Stored Procedure.

-k