Crystal Reports Shows System Tables & Views 1

[Skittle]

I have set up a view in an SQL Server 2000 database.
I have then set up a file dsn for SQL Server authentication to the database which Crystal Reports clients can then use to get at the view.

I have granted SELECT on the view and a Crystal Client user can access the view successfully. The problem is that
when the Crystal client expands the Views and Tables below the connection in Crystal Reports, the user also sees three system objects.

They are a table called dtproperties, a view called sysconstraints and a view called syssegments. No permissions have been specifically assigned to the these system objects but the are by default visable to the public role. Why is this?

I've had to specify a:-
DENY SELECT ON sysconstraints TO MyUserRole
DENY SELECT ON syssegments TO MyUserRole
DENY SELECT ON dtproperties TO MyUserRole
to hide them from crystal reports.

Is there something special about these objects I should know?







Dazed and confused
N+, MCDBA 2000, MCAD .NET

 

[Catadmin]

SQL Server 2000 has a nasty habit of granting Select permissions on objects in a database where the public role has been granted access. There's nothing you can do to fix this other than what you've already done (deny access to the specific objects). And you can't deny complete access to the public role without kiboshing everyone's access to the database.

BTW, this is one of the things they've supposedly fixed in SQL 2005. I specifically asked about it during an MS webcast on upgrading to SQL 2005.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[Skittle]

Ok. Thanks for the info.

Dazed and confused
N+, MCDBA 2000, MCAD .NET