unable to view list of tables/objects

  • jarkley

    Old Hand

    Points: 320

    Before I start Im a novice SQL user. I have instlled MSDE which is a cut down version of SQL 7.0 I have installed SP3. I have created a DB Projectsql, I have created a role called "Project" I have added two NT users DOMAIN\Barbie and DOMAIN\Ken to the role 'Project'. I have granted the role DB_datareader and Db_datawriter to "Project'. I can save a MS project file to the Projectsql db via an ODBC driver for both Barbie & ken but they cannot see each other saved Objects. I am a bit confused as to what security they should be given I would like them to be able to see all saved objects?tables created in the Db . Both users authenticate via NT authentication .

    Thanks from Joe in Sydney Australia

  • Andy Warren

    SSC Guru

    Points: 119676

    Unless you made them part of dbo (dbowner), the objects will get named like this:

    Barbie.table

    Ken.table

    Rather than

    dbo.table

    I avoid ownership chains - rarely worth the hassle. You can fix them after the fact by using sp_changeobjectowner to make dbo the owner.

    Andy

  • jarkley

    Old Hand

    Points: 320

    What is the preferred way to avoid ownership chains. I created domain\ken and domain\barbie. both are members of role 'project' How can they see each others saved ODBC projects in the slq db ?

  • Andy Warren

    SSC Guru

    Points: 119676

    Keep in mind that object ownership is not a simple topic. There are plenty of valid reasons for letting users own their own objects..I think anyway!

    My preferred technique is to put anyone who will be creating objects in the dbowner role - that way SQL makes dbo the owner regardless of which login actually created it. If Im cleaning up after they have been created, then I just modify the object owner - which is what I recommend you do in this case.

    For them to see the objects, you have will have to grant permissions for other users to see them, plus they will have to fully qualify the object, like this:

    select * from barbie.table

    There is plenty to learn and trip over without letting users own objects. Keep it simple for now - you may never find a reason to do anything else!

    Andy

  • mantra

    Newbie

    Points: 2

    Speaking of ownership, I am having a problem with transfering a DTS package from one server to another across NT domains. On the server I am transferring from, I have something like domain1/fflintstone as the object owner. On the destination I am in a different domain with a different user name. Whenever I try and open it, I recieve a "the parameter is not correct" error and it refuses to load it. I attempted saving it directly to the server across the domain and it allowed me to open it, but everytime I go to modify anything it kicks me out of enterprise manager without warning.

    Any ideas?

  • Andy Warren

    SSC Guru

    Points: 119676

  • mantra

    Newbie

    Points: 2

    Thanks Andy! That was the problem. Silly me assumed that all our servers have had SP2 applied because that's what the IT guys are supposed to when they set up the servers 🙂

    Thanks again for the timely reply!

    Denver

Viewing 7 posts - 1 through 7 (of 7 total)

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