Post reply

a very wierd one...mite put ur experience to real test

  • October 9, 2005 at 11:56 pm

    #91413

    Hi  ,

    I am amid resolving a wierd issue.

    The database i am trying to query would let me query all the tables present in the database(it is a replicated database ).there are almost a 1000 of them.I can query them and i get the records.

    But the execution of the stored procedure  "sp_tables"  would return only one table.I was puzzled by this.

    can someone clarify this inconsistency.i was wondering if this is because it is a replicated database

    thank you

     

  • October 10, 2005 at 1:31 am

    #596212

    Please do not cross-post, it just confuses things.

    You've already posted this message

    here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=7&messageid=227531

    and

    here http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=227532

    Which one would you like replies to??

    --------------------
    Colt 45 - the original point and click interface

  • October 10, 2005 at 1:32 am

    #596215

    sp_tables retrieves tables that the user owns and has at least select permissions on one column.

    What is different about the single table that is retrieved and the others that are not retrieved?

  • October 10, 2005 at 11:43 am

    #596345

    Hi David,

     

    thanks a lot for the reply.

    I dont find any difference between this one tables and the rest.May be i am not exploring completely.can u thriw some light on this.

     

     

    Also,

    going by ur reply,the fact that i am able to query all the tables using various forms of select statements ,i shud get them listed using sp_tables .but this is not happening.

    looking forward for ur reply...!!!!

  • October 11, 2005 at 1:50 am

    #596455

    If you supply no arguments to the sp_Tables stored procedure then it drops through to the last query in master.dbo.sp_tables which has a where clause of

    where

    o.name like @table_name

    and user_name(o.uid) like @table_owner

    and o.type in ('U','V','S')

    and charindex(substring(o.type,1,1),@type1) 0 /* Only desired types */

    and permissions (o.id)&4096 0

    I would try

    SELECT o.name , permissions(o.id) as securitypermissions

    from sysobjects o

    where type in('U','S','V')

    and see how the securitypermissions column varies

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply