December 15, 2007 at 9:10 am
I have created a readonly login for the purpose of connecting through Crystal Reports. When I get into Crystal Reports using readonly, no databases appear in the dropdown list. I have done some testing and found that the databases appear if I add readonly to the sysadmin Server Role. I do not want to give readonly all rights, is there another way to make this work without granting sysadmin rights?
The "read only" login has the following properties
- General
SQL Server Authentication
Default database - Master
-Server Roles
Public
-User Mapping
Users Mapped to this login: all databases checked
Role membership: db_accessadmin, db_datareader, public
-Securables
Blank
-Status
Permission to connect to database Engine: Grant
Login: Enabled
All help is appreciated.
December 16, 2007 at 1:43 pm
Seems strange. I have a nearly identical setup, but it works for me (using a SQL Server authentication rather than Windows authentication).
The login should not need any server role membership other than public. This grants enough rights to enumerate the databases on the system. Within the database, the user has db_datareader access (depending on your security requirements, this sort of thing can be tightened up using user-defined db roles).
Have you tried running SQL Profiler to determine exactly what it is that Crystal is trying to do?
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 17, 2007 at 8:18 am
I have a similar setup with a SQL Server 2000 database server.
User: readonly
General
SQL Server Authentication
Default database - Master
-Server Roles
None
-User Mapping
Users Mapped to this login: all databases checked
Role membership: db_datareader, public
-Securables
Blank
-Explicit Permissions
Blank
When connection to Crystal, what method are you using? Crystal Reports Developer or are you using Crystal Objects via Visual Studio 2003 or 2005, etc? There is also ODBC vs. OLE DB as well.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 17, 2007 at 11:32 am
This same setup works fine for me on all my SQL 2000 servers, but this is the first SQL 2005 that I've connected through via Crystal Reports. I created the ODBC using the System DSN and I am connecting via Crystal Reports 9.0 and 10.0. I am having the same issue using both versions. The only way that the databases will populate is if I add the server role "sysadmin" to my readonly login.
Also, when creating the ODBC on the server, I used the sa password, but that has never made a difference before.
Thanks.
December 17, 2007 at 3:17 pm
With the ODBC/DSN settings, I would recommend that you use the "readonly" account as well. By using the "sa" credentials in the DSN you are circumventing the security that is in place for the "readonly" user.
The DSN's configured in my environment all use the "readonly" account (which is a SQL Authenticated User) and do not have issues "seeing" databases.
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 18, 2007 at 8:20 am
I believe the default schema of the "read only" account is not the same schema where all your tables are loaded. Open the read only user from the Database user node and check the default schema (in that database).
You've probably need to set the default schema to DBO and it will work.
Good luck
December 18, 2007 at 12:25 pm
I don't think that will help - the problem is the databases aren't available to be selected, let alone getting to the table level.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
December 18, 2007 at 1:58 pm
Scott, I have no problem with Crystal 10 and a SQL 2005 / OLE DB data source, I can see the databases just fine.
I also have no problem viewing the database with an ODBC system DSN.
The only time I've had trouble is when the default schema is not DBO.
- Doug
January 13, 2008 at 10:34 am
Thanks for all the help guys. Changing the Default schema to DBO resolved the problem. Thanks again.
January 14, 2008 at 8:34 am
Thanks for the reply, I was getting curious.
Best of luck
March 3, 2008 at 3:29 pm
I'm having a similar problem, except that my default schema is not dbo because the tables are owned by a different schema. With this setup crystal does not seem to be able to connect to any tables. Even though the user's default schema is set to the same schema that owns the database tables.
March 3, 2008 at 3:37 pm
That's a bummer. It sounds like Crystal can't find them unless they are owned by DBO. Have your run a SQL profiler trace to see what its trying to do?
Good Luck
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply