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

• Previous message: Kevin McDonnell [MSFT]: "RE: IIS6, VPN, SQL Server 2000"
• Next in thread: Michael, Cheng [MSFT]: "RE: Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)"
• Reply: Michael, Cheng [MSFT]: "RE: Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)"
• Reply: Russell Fields: "Re: Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Mon, 19 Apr 2004 20:11:32 -0500

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

• Previous message: Kevin McDonnell [MSFT]: "RE: IIS6, VPN, SQL Server 2000"
• Next in thread: Michael, Cheng [MSFT]: "RE: Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)"
• Reply: Michael, Cheng [MSFT]: "RE: Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)"
• Reply: Russell Fields: "Re: Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]