MSSQL Security rights for CR 8 report 1

[bashboy]

I have a number of reports accessing a Microsoft SQL Server database. I have created user logins on the SQL Server and given them access to the necessary database. My report designers use these logins.....however, when they try to add a table to an already existing report they can't see all of the table in the database. The only way that I have found to get this to show all available tables in the database is to give the MSSQL login adminstrative rights to the database....obviously I don't want to do this. Does anyone know what rights someone needs on their sql server login so they can add any table from a database they have access to? Thanks,

 

[tlbroadbent]

They simply need Select permissions for each table (and view?) in the database. In SQL 7 and 2000, you can add the users to the database role db_datareader and they will have slect permissions on all tables and views. See SQL BOL for details about adding users to a database role. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions in the SQL Server forum. Many of the ideas apply to all forums.

 

[bashboy]

I have already tried that.....I added the user to the db_datareader and also to the db_datawriter role with no luck. Any other thoughts? Thanks for your response.

 

[timscronin]

First, let's eliminate Crystal from the picture. Can the user use something like query analyzer to run select statements against the DB and the tables. If they don't have QA loaded, load it on one of their machines. Then set up ONE role in the db giving just that role ONLY select to the correct tables. Then add the logins to this role. Then have the user use QA to select against the tables. If they can then it could be a problem with MDAC and Crystal ( I am guessing that might be your problem)

 

[bashboy]

timscronin,

You and I were on the same page. I figured it to be a Crystal problem and not an SQL problem. I did as you advised and my users are able to select from the specified tables without any problems. So, by problem lies with what Crystal needs...big surprise. I have put this same post in the Crystal Data Access forum but have had no responses.....similar to my requests made directly to Crystal themselves. Thanks for your help...much apprectiated.

 

[timscronin]

Which version of Crystal are you using? You might check the MDAC version. We use both 7 and 8 against SQL 2000 and have not seen this problem. One warning. You are probably using Crystal with and ODBC data source. QA uses OLEDB not ODBC. If you have made these changes and the user has not rebooted, have them reboot, ODBC has a nasty habit of caching the ODBC until rebooted. You would be suprised how may times I have seen rebooting fix this.

 

[bashboy]

timscronin

We only use ADO to hit our SQL server......no ODBC. We distribute and use Crystal 7 dll's with our product but we develop in Crystal 8 and 8.5. Out MDAC versioin is 2.61. On SQL 2000. I did not think to try the tried and true reboot.....will do. Thanks again.