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 role-based security

Hi everyone

I have searched the forums for a solution, but i cannot find one that i can see apllies
thus I post.

I am having trouble restricting the access of one of my roles.
Here is the structure of my cube:

Cube: 'Sales'
Dimension : 'Product'
Hierarchy: 'Code - Company'
Member: '123'

I simply need to restrict the user to this member. I have set up the
security in Dimesion data on : Cube>>Dimension and Dimension...
I only selected
[Product].[Code - Company].&[123]
but when viewing a report using this user it doesn't seem to take effect,.
{All the company codes can still be seen in the company code parameter drop down}

When I try to implement this on the cell data tab, I can't see anything in the dropdown
when running the report in the browser

{Using 'Enable read permissions', 'Enable read
contingent Permissions' or a combonation of both}

I'm stuck as can be! Please help to shed some light on
this for me.

I thank you in advance

Gerhard Davids
(PS: If I have been unclear in anyway point it out to me plz
and I shall rephrase it)

Do you have any other roles that the user is a member of that would provide them access to the other company codes? Roles in AS2005 are cumulative, so if a user has access to a set of dimension members via one role but they are restricted to a subset of the dimension members via another role, they will still be able to see all of the dimension members...

HTH,

Dave Fackler

|||

Hi Dave

Thanks for the response.

No, th user is only part of one role so he cannot be overiden by
another. The user is also not part of the Domain, I dont know if that changes anything.
The user is only added on the local-mashine fo the reporting services.

Cube security work when I use the test cube security via the link in
the cell data tab.

G

|||

Two things to check:

1. Run Profiler for AS and make sure that the connection open from RS indeed authenticates as this user

2. Check whether this user is member of Administrators NT group on the machine - if so security doesn't apply to him by default

3. Check response from DISCOVER_CATALOGS while connected as this user and see the content of ROLES column. If it has * in it - it is a bad sign.

|||

Hi Mosha

Thank you for the responce.

I ran the profiler(first time using profiler..) and connected to my ssas
then connected to the reports server and opened the report etc...

The stack trace that was produced did show the correct user authenticated,
The user is also not part of the NT Admin group.

The trace did not show a DISCOVER_CATALOGS and no roles
column was present, however other DISCOVER_ were shown.
Did I do something incorect for this data to be missing?

The only strange thing that i saw was upon clicking the drop down
for the parameter the MDX querry to populate, the 'querry end' event's
error column contained a '1'.

Gerhard

Note: I am using SSAS 2005

|||Anyone have any ideas as to what may be causing this?|||

Hi Gerhard,

Are you using separate Analysis and Reporting servers? If so, the Report server doesn't connect to SSAS using the ID of the report end-user, unless you're using Kerberos - see this past post in the SQL Server OLAP newsgroup:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/ad755b009d23f2e2

>>

microsoft.public.sqlserver.olap > AS + RS

...

Sounds like the classic NT 2-hop authentication problem.
NT credentials can only be passed between two machines (i.e. client and then
RS server). If you attempt to transfer them again from the RS server to the
AS server, then you get a blank username (actually an error, depending on
the OS and its settings). This is a well-known limitation of NT -- it is
totally unrelated to RS or AS. If you really need to do this then you have a
few choices:
1) run RS and AS on the same machine
2) implement kerberos
You could also switch to saved connections on the RS machine, but that would
defeat the dynamic security that you have already established.
--
Dave Wickert [MSFT]
dwick...@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)

>>

Problem with Reporting Server Security. I can not have the correct permissions.

Hi,
I'm trying to configure the RS's Security. i 've created two rules, one
rule for administrators and the other one for IUSR_machine. I connect to
Windows 2000 server as administrator.
The problem is that when i access the Report Administrator via URL, it
applies the rules of the IUSR_machine, so i can't make administrator tasks.
¿Why does the Report Administrator think that I am an IUSR_machine user, and
how can I connect as administrator?
Please, anybody can help me,
thanks in advance.
Oliver.I am able to reproduce your issue when I set my report manager site to allow
anonymous access. When I do this, it ignores my windows authentication
forcing the report manager to treat me as a consumer, not an admin. This is
not the default setting. Did someone change it afterwards? If you find this
to be the case, uncheck the "Anonymous Access" box and try it again. Make
sure "Integrated Windows Authentication" is checked at the bottom of the
Authentication Methods dialog.
({IIS Manager}=>{Default Web Site}=>{Reports}=>{Properties}=>{Directory
Security}=>{Edit}).
"oli" wrote:
> Hi,
> I'm trying to configure the RS's Security. i 've created two rules, one
> rule for administrators and the other one for IUSR_machine. I connect to
> Windows 2000 server as administrator.
> The problem is that when i access the Report Administrator via URL, it
> applies the rules of the IUSR_machine, so i can't make administrator tasks.
> ¿Why does the Report Administrator think that I am an IUSR_machine user, and
> how can I connect as administrator?
>
> Please, anybody can help me,
> thanks in advance.
> Oliver.
>
>|||Thanks for your response.
I have verifed the Security in IIS. When I configured the authentication I
activated the Report Server with "anonymous access", but I configured the
Report Manager only with "Integrated Windows Authentication". With this
configuration, the RS works in the way I wrote in the first mail.
Oliver.
>I am able to reproduce your issue when I set my report manager site to
>allow
> anonymous access. When I do this, it ignores my windows authentication
> forcing the report manager to treat me as a consumer, not an admin. This
> is
> not the default setting. Did someone change it afterwards? If you find
> this
> to be the case, uncheck the "Anonymous Access" box and try it again. Make
> sure "Integrated Windows Authentication" is checked at the bottom of the
> Authentication Methods dialog.
> ({IIS Manager}=>{Default Web Site}=>{Reports}=>{Properties}=>{Directory
> Security}=>{Edit}).
>
> "oli" wrote:
>> Hi,
>> I'm trying to configure the RS's Security. i 've created two rules,
>> one
>> rule for administrators and the other one for IUSR_machine. I connect to
>> Windows 2000 server as administrator.
>> The problem is that when i access the Report Administrator via URL, it
>> applies the rules of the IUSR_machine, so i can't make administrator
>> tasks.
>> ¿Why does the Report Administrator think that I am an IUSR_machine user,
>> and
>> how can I connect as administrator?
>>
>> Please, anybody can help me,
>> thanks in advance.
>> Oliver.
>>|||If i put both report server and report administration with integrated
windows authentication it's goes fine, but the problems appears when i
configure report administration with itegrated windows authentication and
report server with anonymous access.
Oliver
"oli" <oli1350@.hotmail.com> escribió en el mensaje
news:eu%23AV%23IpEHA.3900@.TK2MSFTNGP10.phx.gbl...
> Thanks for your response.
> I have verifed the Security in IIS. When I configured the authentication I
> activated the Report Server with "anonymous access", but I configured the
> Report Manager only with "Integrated Windows Authentication". With this
> configuration, the RS works in the way I wrote in the first mail.
>
> Oliver.
>
>>I am able to reproduce your issue when I set my report manager site to
>>allow
>> anonymous access. When I do this, it ignores my windows authentication
>> forcing the report manager to treat me as a consumer, not an admin. This
>> is
>> not the default setting. Did someone change it afterwards? If you find
>> this
>> to be the case, uncheck the "Anonymous Access" box and try it again. Make
>> sure "Integrated Windows Authentication" is checked at the bottom of the
>> Authentication Methods dialog.
>> ({IIS Manager}=>{Default Web Site}=>{Reports}=>{Properties}=>{Directory
>> Security}=>{Edit}).
>>
>> "oli" wrote:
>> Hi,
>> I'm trying to configure the RS's Security. i 've created two rules,
>> one
>> rule for administrators and the other one for IUSR_machine. I connect to
>> Windows 2000 server as administrator.
>> The problem is that when i access the Report Administrator via URL, it
>> applies the rules of the IUSR_machine, so i can't make administrator
>> tasks.
>> ¿Why does the Report Administrator think that I am an IUSR_machine user,
>> and
>> how can I connect as administrator?
>>
>> Please, anybody can help me,
>> thanks in advance.
>> Oliver.
>>
>

Problem with Report Server Authentication.

Hi there,
I'm having this weird issue here on the report server. I am trying to
configure security on the Report Server but right now all users have access
to all folder on the report server even though the only group that I have
access to the report manager and it's contents is the DomainName\
Administrator which is me.
On the IIS, I have disable the anomymous access box and I have enabled the
Integrated windows Authentication option in there. now with all this I'm
amazed that all users that click on the Report Server link are able to do
everything on the report server. even when I assigned that user to a Role,
let's say Browser, this user is still able to see every single folder I have
created there.
Please shed some light on this issue.
Thanks,
Manny
--
Message posted via http://www.sqlmonster.comHi Manny
Try to click on the folder where you put all reports (i.e. MyReports) then
click on Properties Tab and then on Securty link button. Add users do you
want to see the reports by clicking on "New Role Assigment" button, ask "ok"
to next window. Type a valid domain user and check the roles do you want to
grant to, usually "Explorer". Repeat the same for all users do you want to
grant permissions.
I hope I helped you.
Sincerely,
Arturo Carrión
"Manny123 via SQLMonster.com" <u30518@.uwe> escribió en el mensaje
news:6d5c702f49cb3@.uwe...
> Hi there,
> I'm having this weird issue here on the report server. I am trying to
> configure security on the Report Server but right now all users have
> access
> to all folder on the report server even though the only group that I have
> access to the report manager and it's contents is the DomainName\
> Administrator which is me.
> On the IIS, I have disable the anomymous access box and I have enabled the
> Integrated windows Authentication option in there. now with all this I'm
> amazed that all users that click on the Report Server link are able to do
> everything on the report server. even when I assigned that user to a Role,
> let's say Browser, this user is still able to see every single folder I
> have
> created there.
> Please shed some light on this issue.
> Thanks,
> Manny
> --
> Message posted via http://www.sqlmonster.com
>