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

From: Russell Fields (RussellFields_at_NoMailPlease.Com)
Date: 04/20/04 

Date: Tue, 20 Apr 2004 09:05:44 -0400

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
>
>

Relevant Pages

  • Re: Adds the group to the database But Security EM
    ... > I view the database with SQL Server Enterprise Manager in the database and ... > the SQL Server security domain group. ... --grant group permissions to connect to SQL Server ...
    (microsoft.public.sqlserver.security)
  • Re: Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)
    ... then you will need to make domain logins be users in the database. ... You should not need to grant any rights to the users, ... > This app works fine if we use a domain user and grant it access to the ... > db - instead the user is member of a domain group and this group is added ...
    (microsoft.public.sqlserver.programming)
  • Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)
    ... Recently we bought a 3rd party application that connects to our database ... This app works fine if we use a domain user and grant it access to the ... db - instead the user is member of a domain group and this group is added to ... I tracked that the app uses an sp to ...
    (microsoft.public.sqlserver.security)
  • Problem with sp_tables_rowset;2 (Domain Users vs Domain Groups)
    ... Recently we bought a 3rd party application that connects to our database ... This app works fine if we use a domain user and grant it access to the ... db - instead the user is member of a domain group and this group is added to ... I tracked that the app uses an sp to ...
    (microsoft.public.sqlserver.programming)
  • SQL Server 2005 access from domain group
    ... How do I grant access to a domain group and give it rights within ... a database to select from a view and execute a procedure say? ...
    (microsoft.public.sqlserver.setup)