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.
) and how can I resolve it? I'd like to use all advantages of masterpages and datasources together.
Waseem Basheer - MSFT wrote: