Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)

Recently we bought a 3rd party application that connects to our database
(using SQL Server OLEDB Provider and Integrated Security). One of the step
it does is to bring the list of table that user has access to.
This app works fine if we use a domain user and grant it access to the
database "individually". But it fails to retrieve list of tables if we use
the "Domain group" security model. i.e. a domain user does not exists in
db - instead the user is member of a domain group and this group is added to
database.
t
Using profiler, I tracked that the app uses an sp (sp_tables_rowset;2) to
retrieve list of tables. In "both" cases, the app was able to connect to
database without any problem. But this sp returns zero rows in case of
domain group. My aim is to minimize access rights management on the database
and do most of it on Active Directory - which is why I want to use domain
groups instead of individual domain users.
By reviewing the code below of the sp, a limit to the rows is done thru'
this clause: u.uid = user_id(). But a call to this method in query analyzer
"select user_id()", returns 0.
Here is the line that app uses to call the sp:
exec [mytestdb]..sp_tables_rowset;2 N'MyDomain\MyUser', NULL
Here is the code for the sp (i found on google):
create procedure sp_tables_rowset;2
(
@.table_schema varchar(255) = null,
@.table_type varchar(255) = null
)
as
select TABLE_CATALOG = db_name(),
TABLE_SCHEMA = user_name(o.uid),
TABLE_NAME = o.name,
TABLE_TYPE = convert(varchar(30),
case o.type
when 'U' then 'TABLE'
when 'V' then 'VIEW'
when 'S' then 'SYSTEM TABLE'
end),
TABLE_GUID = convert(binary(16), null),
DESCRIPTION = convert(varchar(1), null)
from sysusers u, sysobjects o
where o.type in ('U','V','S')
and ( @.table_schema is null
or @.table_schema = user_name(o.uid)
)
and (
@.table_type is null
or @.table_type = case o.type
when 'U' then 'TABLE'
when 'V' then 'VIEW'
when 'S' then 'SYSTEM TABLE'
end
)
and u.uid = user_id() /* constrain sysusers uid for use in subquery */
and (
suser_id() = 1 /* User is the System Administrator */
or o.uid = user_id() /* User created the object */
/* here's the magic... select the highest precedence of permissions in the
order (user,group,public) */
or ( (select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
from sysprotects p
/* join to correlate with all rows in sysobjects */
where p.id = o.id
/* get rows for public,current user,user's group */
and (p.uid = 0 or p.uid = user_id() or p.uid = u.gid)
/* check for SELECT,EXECUTE privilege */
and (action in (193,224)))&1 /* more magic...normalize GRANT */
) = 1 /* final magic...compare Grants */
)
order by 4, 2, 3Hi Salman,
First of all, You could see the codes of sp_tables_rowset;2 by doing
1. Open Query Analyzer as sa
2. use master
3. sp_helptext 'sp_tables_rowset'
you could see the definition of sp_tables_rowset;2, which have little
difference with the codes you given. Would you please tell me where do you
get that code?
Secondly, sp_tables_rowset is used to show table information, while the
first parameter is used to identify TABLE_SCHEMA, and the second one is
used to identify TABLE_TYPE. By running command exec sp_tables_rowset;2 ,
you could see all the tables in current database. Would you please confirm
that your Domain Users and Domain Groups both exist in TABLE_SCHEMA?
Thirdly, I did the following testing based on my understanding,
0. I tried it in Windows XP / 2003 English Version and SQL Server 2000 (SP3)
create a "testdb" database
1. Open Comuter Management, add a Group named "testGP" and an user named
"testUs" in Local Users and Groups separatly
2. Open SQL Server Entriprise Manager
adding 'testUs' and 'testGP' to Security -> Logins, give them
different psw
3. using testdb as default database and click testdb in Database Access
page.
4. confirm that "testUs" and "testGP" could be found at Database -> testdb
-> Users
5. Using "testUs" and "testGP" to login in with Query Analyzer
6. create a table separatly by using "testUs" and "testGP"
7. running exec sp_tables_rowset;2
running exec sp_tables_rowset;2, 'testUs'
running exec sp_tables_rowset;2, 'testGP'
and I will get the answer as expected, no matter what I logined as
8. running select user_id(), I could get different number, in my test, it's
5 and 6.
Bottom line - have a good backup strategy before any modifications.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Salman,
It seems that sp_tables_rowset as it is being called is attempting to list
tables that are "owned" by the user, or (in new terms) where the schema name
is the same as the user_name.
Are there actually supposed to be different versions of a table? Such as:
mytestdb.Salman.FirstTable
mytestdb.Joseph.FirstTable
mytestdb.Richie.FirstTable
That seems unlikely to me, but certainly not impossible. If that is the
case, then you will need to make domain logins be users in the database.
You should not need to grant any rights to the users, their rights will come
through the domain groups, but the actual row in sysusers is necessary for
this all to work.
Are all objects in the database owned by dbo? If so, then the call to
sp_tables_rowset should be:
exec [mytestdb]..sp_tables_rowset;2 N'dbo', NULL
The application is obviously making some assumptions about how you connect
to the database, and what rights the user has in the database, that are
different from what you are expecting. (Yes, we have had some interesting
times with commercial software as well, but not this particular problem.)
If your problems persist, you will need to discuss this issue in detail with
your vendor to determine what assumptions they have made.
Russell Fields
"Salman" <salman_z_g@.hotmail.com> wrote in message
news:ekgaFSnJEHA.2776@.TK2MSFTNGP12.phx.gbl...
> Recently we bought a 3rd party application that connects to our database
> (using SQL Server OLEDB Provider and Integrated Security). One of the step
> it does is to bring the list of table that user has access to.
> This app works fine if we use a domain user and grant it access to the
> database "individually". But it fails to retrieve list of tables if we use
> the "Domain group" security model. i.e. a domain user does not exists in
> db - instead the user is member of a domain group and this group is added
to
> database.
> t
> Using profiler, I tracked that the app uses an sp (sp_tables_rowset;2) to
> retrieve list of tables. In "both" cases, the app was able to connect to
> database without any problem. But this sp returns zero rows in case of
> domain group. My aim is to minimize access rights management on the
database
> and do most of it on Active Directory - which is why I want to use domain
> groups instead of individual domain users.
> By reviewing the code below of the sp, a limit to the rows is done thru'
> this clause: u.uid = user_id(). But a call to this method in query
analyzer
> "select user_id()", returns 0.
> Here is the line that app uses to call the sp:
> exec [mytestdb]..sp_tables_rowset;2 N'MyDomain\MyUser', NULL
> Here is the code for the sp (i found on google):
> create procedure sp_tables_rowset;2
> (
> @.table_schema varchar(255) = null,
> @.table_type varchar(255) = null
> )
> as
> select TABLE_CATALOG = db_name(),
> TABLE_SCHEMA = user_name(o.uid),
> TABLE_NAME = o.name,
> TABLE_TYPE = convert(varchar(30),
> case o.type
> when 'U' then 'TABLE'
> when 'V' then 'VIEW'
> when 'S' then 'SYSTEM TABLE'
> end),
> TABLE_GUID = convert(binary(16), null),
> DESCRIPTION = convert(varchar(1), null)
> from sysusers u, sysobjects o
> where o.type in ('U','V','S')
> and ( @.table_schema is null
> or @.table_schema = user_name(o.uid)
> )
> and (
> @.table_type is null
> or @.table_type = case o.type
> when 'U' then 'TABLE'
> when 'V' then 'VIEW'
> when 'S' then 'SYSTEM TABLE'
> end
> )
> and u.uid = user_id() /* constrain sysusers uid for use in subquery */
> and (
> suser_id() = 1 /* User is the System Administrator */
> or o.uid = user_id() /* User created the object */
> /* here's the magic... select the highest precedence of permissions in
the
> order (user,group,public) */
> or ( (select max(((sign(uid)*abs(uid-16383))*2)+(protecttype&1))
> from sysprotects p
> /* join to correlate with all rows in sysobjects */
> where p.id = o.id
> /* get rows for public,current user,user's group */
> and (p.uid = 0 or p.uid = user_id() or p.uid = u.gid)
> /* check for SELECT,EXECUTE privilege */
> and (action in (193,224)))&1 /* more magic...normalize GRANT */
> ) = 1 /* final magic...compare Grants */
> )
> order by 4, 2, 3
>|||Hi Salman,
Moreover, please confirm that you have make the latest upgrades, which
could be download at
Microsoft SQL Server 2000 Service Pack 3a
http://www.microsoft.com/downloads/...d52c-0488-4e46-
afbf-acace5369fa3&DisplayLang=en
If I have some misunderstandings, please show me your steps as detailed as
possible that I could reporduce your situation on my machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Thanks for your reply.
Responses are inline.
""Michael, Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in messa
ge
news:gPcXmYtJEHA.480@.cpmsftngxa10.phx.gbl...
> Hi Salman,
> First of all, You could see the codes of sp_tables_rowset;2 by doing
> 1. Open Query Analyzer as sa
> 2. use master
> 3. sp_helptext 'sp_tables_rowset'
> you could see the definition of sp_tables_rowset;2, which have little
> difference with the codes you given. Would you please tell me where do you
> get that code?
I think i found the sp at this locations:
http://www.cis.usouthal.edu/share/N...all/instcat.sql
You are right - ran sp_helptext on my db and got a different one.
Where can I find documentation (usage scenarios) for this & similar sp?
I couldn't find it on msdn. How do 3rd party app vendors utilize these
stored procs if they are not searchable on msdn?

> Secondly, sp_tables_rowset is used to show table information, while the
> first parameter is used to identify TABLE_SCHEMA, and the second one is
> used to identify TABLE_TYPE. By running command exec sp_tables_rowset;2 ,
> you could see all the tables in current database. Would you please confirm
> that your Domain Users and Domain Groups both exist in TABLE_SCHEMA?
Is this compulsary to have domain users there? If yes - that means every
time I want a user added to have access, I will have to add it to the
database as well as the Active Directory group - which kind of defeats the
purpose of the group. Plz correct me if I m wrong. I might be missing some
basics ... please forward me some links to read about this?
Anyway, I tried it both ways. I have two domain users dom\usr1 and dom\usr2
in a domain group dom\grp1.
In my database -> Users, I can see dom\usr1 and dom\grp1 - but not dom\usr2.
Tried running sp_tables_rowset;2 '<username>' through both users and got no
results (because of the reason below).
I researched a little more on the problem. The 3rd party app is using
sp_oledb_ro_usrname first to find the user name & state (i think) of
database. And then it exec the sp_tables_rowset;2 to get list of tables and
passes the user name of prev sp as the table schema parameter value. In our
case, our target database is under table schema of dbo. So any call with
those user names specified returns no rows.
Waiting for ur reply.
Thanks again.

> Thirdly, I did the following testing based on my understanding,
> 0. I tried it in Windows XP / 2003 English Version and SQL Server 2000
(SP3)
> create a "testdb" database
> 1. Open Comuter Management, add a Group named "testGP" and an user named
> "testUs" in Local Users and Groups separatly
> 2. Open SQL Server Entriprise Manager
> adding 'testUs' and 'testGP' to Security -> Logins, give them
> different psw
> 3. using testdb as default database and click testdb in Database Access
> page.
> 4. confirm that "testUs" and "testGP" could be found at Database -> testdb
> -> Users
> 5. Using "testUs" and "testGP" to login in with Query Analyzer
> 6. create a table separatly by using "testUs" and "testGP"
> 7. running exec sp_tables_rowset;2
> running exec sp_tables_rowset;2, 'testUs'
> running exec sp_tables_rowset;2, 'testGP'
> and I will get the answer as expected, no matter what I logined as
> 8. running select user_id(), I could get different number, in my test,
it's
> 5 and 6.
> Bottom line - have a good backup strategy before any modifications.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
> Sincerely yours,
> Michael Cheng
> Microsoft Online Support
> ****************************************
*******************
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks.
>|||Hi Salman,
Based on my understanding, when you add a domain group to Login, it is
TREATED as an "individual" user, which is to say, you could not login as
domain group\domain users when you just add domain groups as Login. You
could just login is using domain group name and its password instead of its
users.
If you want to all users to be under the same group login to SQL Server,
you'd better make a login for this group. All users under this group will
use the group name to login. That's why you could not find result by
running sp_tables_rowset;2 'dom\usr2' as you just add dom\grp1 into your
Login.
Moreover, we cannot provide documents for sp_tables_rowset as it is not
recommend. In the meanwhile, I recommend you have a look at
Administering SQL Server - Managing Security
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_security_8pkj.asp
which describes the security tools built into Microsoft SQL Server 2000 and
includes information about : Security Architecture and Managing Security
Accounts
Hope this helps. Please also feel free to post in the group if you would
like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Hi Salman,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

Problem with Prepared Statements

Hi
we are developing a java application, which connects to a MS SQL Server 2000
via the Microsoft JDBC Drive Service Pack 3.
Due to the fact that we have massiv performance problems with MS SQL. I
started profiling our MS SQL Server.
I've recognized during analyzing the traces, that the variables of every
prepared Sql command are defined as nvarchar(4000) or Decimal(38,0). Quite
confusing by the fact that the lenght of the fields is not exceeding 50
signs in the tables as well as in the variable used within our application.
I not understand the behaviour of the JDBC Driver. Is there a problem
defining the length of prepared parameters ?
Cause within ODBC i am able to define the length of input values in order to
reduce the amount of data being in access by the sql engine.
kind regards
chris
Christian Billig wrote:

> Hi
> we are developing a java application, which connects to a MS SQL Server 2000
> via the Microsoft JDBC Drive Service Pack 3.
> Due to the fact that we have massiv performance problems with MS SQL. I
> started profiling our MS SQL Server.
> I've recognized during analyzing the traces, that the variables of every
> prepared Sql command are defined as nvarchar(4000) or Decimal(38,0). Quite
> confusing by the fact that the lenght of the fields is not exceeding 50
> signs in the tables as well as in the variable used within our application.
> I not understand the behaviour of the JDBC Driver. Is there a problem
> defining the length of prepared parameters ?
> Cause within ODBC i am able to define the length of input values in order to
> reduce the amount of data being in access by the sql engine.
> kind regards
> chris
Hi. The declared length of the varchar variables are not crucial, but
if you have real VARCHAR columns, and the driver is sending data as
NVARCHARs, then your varchar indexes won't be used, so you will have
slow table scans. The solution for that is to set the JDBC connection
property SendStringParametersAsUnicode=false. The driver by default
sends data as 16-bit characters so any Java char is sent correctly.
As long as you are using standard English 8-bit ASCII characters,
you don't need to send as UNICODE.
Let me know if this takes care of the problem.
Joe
|||Hi Joe
setting the property SendStringParametersAsUnicode=false did not change the
performance.
What still is confusing are the Trace Results.
Executing a SQL Statement as prepared statement in a loop just changing the
binded values for 50 times,
is resulting for Java in a trace with 50 statements exec sp_cursoropen.
Doing the same in c# und using an OleDB Connection with the exact same SQL
and value is leading to a different Trace
which contains one statement with "exec sp_prepexec" and 49 statements of
"exec sp_execute".
May i'm doing something wrong ? Or is that really the right behaviour ? It
looks to me that in our java application the PrepareStatement is not really
executed.
bye
chris
|||Seems to be a problem of the JDBC Driver even prepare Insert and Update
statements are being executed as sp_executesql
where as using the jdbc 2005 beta 1 driver they are executed as sp_prepexec
and sp_execute.
Even Select statements which were executed as cursor-open, -fetch and -close
are now executed as sp_prepexec and sp_execute as long as the resultset is
text_forward_only.
Due to the fact that we need scrollable resultsets the former behaviour is
ok
During testing the JDBC 2005 Beta 1 we recognized a bug in this
constellation.The SQL Statement used for opening the cursor is not prepared
any longer although we are using the preparedstatement method. That causes
an immens memory usage until the SQL Server reachs the allowed maximum
memory usage and begins to use the swap file.
|||forgot the log
Both Statements were executed as PreparedStatement with an insensitive
Cursor ResultSet.
JDBC 2000 on SQL Server 2000 SP4
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=4104
declare @.P3 int
set @.P3=8193
declare @.P4 int
set @.P4=0
exec sp_cursoropen @.P1 output, N'SELECT "LVORLP".* FROM "LVORLP" WHERE
("LVBNKZ" = @.P1 AND "LVFIRM" = @.P2 AND ("LVABRJ" > @.P3 OR "LVABRJ" = @.P4 AND
"LVABRM" > @.P5 OR "LVABRJ" = @.P6 AND "LVABRM" = @.P7 AND "LVABGR" > @.P8 OR
"LVABRJ" = @.P9 AND "LVABRM" = @.P10 AND "LVABGR" = @.P11 AND "LVSABE" > @.P12
OR "LVABRJ" = @.P13 AND "LVABRM" = @.P14 AND "LVABGR" = @.P15 AND "LVSABE" =
@.P16 AND "LVPRNR" >= @.P17)) ORDER BY "LVABRJ", "LVABRM", "LVABGR", "LVSABE",
"LVPRNR"', @.P2 output, @.P3 output, @.P4 output, N'@.P1 varbinary(8000) ,@.P2
varbinary(8000) ,@.P3 decimal(38,0) ,@.P4 decimal(38,0) ,@.P5 decimal(38,0)
,@.P6 decimal(38,0) ,@.P7 decimal(38,0) ,@.P8 varbinary(8000) ,@.P9
decimal(38,0) ,@.P10 decimal(38,0) ,@.P11 varbinary(8000) ,@.P12
varbinary(8000) ,@.P13 decimal(38,0) ,@.P14 decimal(38,0) ,@.P15
varbinary(8000) ,@.P16 varbinary(8000) ,@.P17 decimal(38,0) ', 0x42,
0x303031, -9999, -9999, -99, -9999, -99, 0x00, -9999, -99, 0x00,
0x000000, -9999, -99, 0x00, 0x000000, -999999
select @.P1, @.P2, @.P3, @.P4
JDBC 2005 Beta 1 on SQL Server 2000 SP4
RPC Starting
declare @.P1 int
set @.P1=0
declare @.P2 int
set @.P2=0
exec sp_cursoropen @.P1 output, 'SELECT "LVORLP".* FROM "LVORLP" WHERE
(("LVBNKZ" > 0x42) OR ("LVBNKZ" = 0x42 AND "LVFIRM" > 0x303031) OR ("LVBNKZ"
= 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" > 2005) OR ("LVBNKZ" = 0x42 AND
"LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" > 9) OR ("LVBNKZ" =
0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" = 9 AND
"LVABGR" > 0x20) OR ("LVBNKZ" = 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" =
2005 AND "LVABRM" = 9 AND "LVABGR" = 0x20 AND "LVSABE" > 0x202020) OR
("LVBNKZ" = 0x42 AND "LVFIRM" = 0x303031 AND "LVABRJ" = 2005 AND "LVABRM" =
9 AND "LVABGR" = 0x20 AND "LVSABE" = 0x202020 AND "LVPRNR" >= 0)) ORDER BY
"LVBNKZ", "LVFIRM", "LVABRJ", "LVABRM", "LVABGR", "LVSABE", "LVPRNR"', 8, 1,
@.P2 output
select @.P1, @.P2