MS SQL Server (Express) 2005 problem

[rafig]

I am developing a system using MS SQL 2005 and I’m using the MS JDBC driver (although I also tried using the jTDS driver as well).
My problem is that when I create a database in the back-end, and look at it in Servoy, I see all the System Tables that get created as well (and there are a LOT of them!).
I just want to be able to see my User Tables.
Does anyone know how I can make Servoy only show the User Tables?
I thought that maybe I could use the ‘Catalog’ or ‘Schema’ option in the ‘DB Servers’ tab in Prefs., but I can’t find any (useful) documentation on these options.
Maybe another option on the Database URL, or using the jTDS driver in a different way?
I can obviously still work and develop, but it is annoying.

Any help greatly appreciated.

Thanks,
Rafi

Servoy Developer
Version 3.0.1-build 372
Java version 1.5.0_06-64 (Mac OS X)

[IT2Be]

So, basically you are asking for a filter. Nice idea but right now not possible…

[Jan_Aleman]

normally servoy doesnt show system tables so it’s either your particular jdbc driver or server config that’s doing it. Obviously logging in as ‘sa’ is not a good idea either, create a user with less rights.

[ledergerber]

Hello Rafi,

have the same appearance with MS SQL Server 2005 and Servoy 3 here on a WinXP. But I also use “sa” for logging in.
If I find a solutions on that, I let you know.
Btw, with MS SQL Server 2005 and Servoy 2.x it is working fine.

Thomas

[rafig]

Hi.
I tried using a different log-in account and also tried changing back to the jTDS driver, but I still see all the system tables (I had to give the user ‘sysadmin’ privs, otherwise I didn’t see my user tables at all).
I also tried in Servoy 2.2.6, and that doesn’t show me the System Tables with the same driver and settings, so maybe it is an issue in 3.0.1?

[janteque]

I’m facing the same problem now…

However I’m getting closer, I created a new user and I modified each system view and remove SELECT privileges to database function “public” on the MSSQL Server Management Studio.

This seems to remove the view from the list on Servoy, but I don’t really now it it’ll have some sort of “collateral” effect…

Besides, this is a tedious work (there are more than 290 views…)

any ideas on this subject?

[patrick]

This is not a matter of Servoy, but of the new way SQL Server handles these system information tables (and views). Basically you need to configure the user’s rights in a way that these tables and views are not accessible by the Servoy user. I have spent hours to find out how to do that but still haven’t found a simple way to do it. So if somebody does or did, please post that here.

The worst about it is not the amount of (useless) tables you see, but their names. For example, there is a system view called “messages”. That really messes things up. Another one is called “events”, which is also a table name of ours and creates confusion.

The bad part of the story is that you cannot use SQL Server 2000 on Vista…

[rafig]

I think Servoy can do something about it, as they have hidden them from the data providers screen, but not in other areas (I am using 3.1.7, can’t comment on 3.5.1…)

Rafi

[janteque]

I think Servoy keep these views, but in the dataproviders screen are classified under the “views” node.

Maybe to separate tables and views in the new form window would be a good idea…

P.S. I’ve edited my last post with a sort of solution I’ve found…

[patrick]

Your approach is the same I used. But that is one hell of a job. I can’t believe there is no other way.

[martinh]

I think Servoy should filter the tables. I guess you don’t want to use SQL Server systemtables in your solution. If you need to be database independent, you would never use sysemtables of any database.

There are 2 ways Servoy can select the user-tables from SQL Server 2005:

select TABLE_NAME from INFORMATION_SCHEMA.TABLES

or

select name from sys.sysobjects where type = 'U'

The first query is a systemview on the sysobjects table, so I guess that would be the best solution. If Servoy should also show views, then the following query is needed also:

select TABLE_NAME from INFORMATION_SCHEMA.VIEWS

or

select name from sys.sysobjects where type = 'V'

[patrick]

I don’t think we can expect Servoy to dig into the logic behind this for every database on the market… What if SQL Server 2009 comes out? Do we then need a Servoy update, because MS has changed things a bit?

[Jan_Aleman]

Servoy uses a standard JDBC call to get tables. We prefer not to hardcode calls as per your suggestion to maintain cross database behavior. We will see what can be done about it, obviously the best would be if Microsoft would just follow the standards but I have a feeling that that is not going to happen.

[patrick]

Microsoft tries to do us a favor by providing these views…

[janteque]

No matter what I try it seems not possible to remove system views from the list on Servoy. Not even removing “select” privileges on this views does the trick.

I guess that the best option would be to add a “schemaFilter” parameter on the connection properties so Servoy could filter and show only tables and views in that schema.

If Servoy uses standard JDBC to get tables and views, its code would be similar to this:

DatabaseMetaData dbm = con.getMetaData();
String[] types = {"TABLE", "VIEW"};
ResultSet rs = dbm.getTables(null,null,"%",types);
while (rs.next()){
}

I the “schemaFilter” property is provided, then Servoy could do a filtering like this:

DatabaseMetaData dbm = con.getMetaData();
String[] types = {"TABLE", "VIEW"};
ResultSet rs = dbm.getTables(null,schemaFilter,"%",types);
while (rs.next()){
}

This feature would help us people using sql server 2005, but it may also be handy to people to tidy up their connections.

I’m just guessing how Servoy gets all tables and views and maybe I’m fully wrong, but I’ve to try…

[erdione]

I’ve made a little sql script for the problem, it’s based on the sqlserver user security and the servoysqlaccount (it must not be the sa user)

I explicitly DENY the SELECT right on each system views for the servoysqlaccount

Now i can’t see the system views in Servoy

I don’t want to publish it because i don’t really know the impact of my sql scripts on SQL or Servoy behaviour

mail me if you want me to send the sql script and help me to test it .

[rgansevles]

janteque:
I guess that the best option would be to add a “schemaFilter” parameter on the connection properties so Servoy could filter and show only tables and views in that schema.

I the “schemaFilter” property is provided, then Servoy could do a filtering like this:

DatabaseMetaData dbm = con.getMetaData();

String types = {“TABLE”, “VIEW”};
ResultSet rs = dbm.getTables(null,schemaFilter,“%”,types);
while (rs.next()){
}

This is exactly what we already do, we pass the catalog and the schema from the server definition to the getTables() call.
The database should not return the system views.

Rob

[janteque]

Rob, if I put the schema or catalog property on the server definition I get an SQL error

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect sintax near key word 'schema'.

It’d be great if we could use this feature to filter views or tables on SQL Server 2005.

[erdione]

This is the little script to hide the system views from servoy that have been tested.

First of all you must have an sqlservoyaccount that you use with servoy to connect to your db server (other than ‘sa’ of course) with GRANTED db_owner right on your database “yourdbname” and GRANTED guest rights on the master db
The idea is to explicitly DENY the select right on system views for your SQLservoyaccount
You can use it searching and replacing yourdbname by your db name and SQLservoyaccount by your sqlaccount
If you want to remove to DENY right and go back to the original security state you must execute the same script replacing the DENY SELECT ON by REVOKE SELECT ON

USE [yourdbname]
DECLARE @View sysname
DECLARE @command varchar(2000) 
DECLARE cView CURSOR LOCAL FOR SELECT v.name FROM sys.all_views AS v WHERE (v.type = 'V')and(SCHEMA_NAME(v.schema_id)=N'sys') order by Name asc 
OPEN cView 
FETCH NEXT FROM cView INTO @View
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @command= 'DENY SELECT ON [sys].[' + @View  +'] TO [SQLservoyaccount]'
  EXEC (@command)
  FETCH NEXT FROM cView INTO @View 
 END
 CLOSE cView 
 DEALLOCATE cView 
GO
 
USE [master]
DECLARE @View sysname
DECLARE @command varchar(2000) 
DECLARE cView CURSOR LOCAL FOR SELECT v.name FROM sys.all_views AS v WHERE (v.type = 'V')and(SCHEMA_NAME(v.schema_id)=N'sys') order by Name asc 
OPEN cView 
FETCH NEXT FROM cView INTO @View
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @command= 'DENY SELECT ON [sys].[' + @View  +'] TO [SQLservoyaccount]'
  EXEC (@command)
  FETCH NEXT FROM cView INTO @View 
 END
 CLOSE cView 
 DEALLOCATE cView 
GO
 

USE [master]
DECLARE @View sysname
DECLARE @command varchar(2000) 
DECLARE cView CURSOR LOCAL FOR SELECT v.name FROM sys.all_views AS v WHERE (v.type = 'V')and(SCHEMA_NAME(v.schema_id)=N'INFORMATION_SCHEMA') order by Name asc 
OPEN cView 
FETCH NEXT FROM cView INTO @View
 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @command= 'DENY SELECT ON [INFORMATION_SCHEMA].[' + @View  +'] TO [SQLservoyaccount]'
  EXEC (@command)
  FETCH NEXT FROM cView INTO @View 
 END
 CLOSE cView 
 DEALLOCATE cView 
GO

[janteque]

Thanks Fabrice, it seems to work fine!