Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002

    Unanswered: Sharing tables btw. users in same NT group

    This is the case:
    I'm having X users in my NT domain. A minor number of these is member og a NT Group: dbusers.
    In SQL Server I have created a login for dbusers.
    The dbusers has been granted rights to the database MYDB.

    Now user Peter (member of the dbusers grouop) connects to SQL Server. He creates a table mytab, and becomes the owner of this table. Afterwards he grants select-rights to public and to dbusers.

    Later on user Bob (also a member of dbusers) connects to SQL Server. The problem is: He cannot select from mytab. Neither from MYDB.Peter.mytab. In both case this error occours:
    Server: Msg 208, Level 16, State 1, Line 1
    Invalid object name 'mytab'.

    What's wrong ?

    Is it possibly for a GROUP to be owner of a table ? Or does it have to be one specific user, who is tableowner. How can other users make selects (and inserts etc) in that table ? Notice: The user has NOT got his own login, but i authorized to SQL Server via his NT GROUP account.

    Pleas help


  2. #2
    Join Date
    Oct 2001
    It is unusual to allow users to create tables as this will mean that the dba has no control over what is going on.
    If you are allowing your users to change the database structure then they should be given the rights to control what is going on meaning that they will need to be mapped to dbo in the database which will allow them to access all tables and should create them as owned by dbo.

  3. #3
    Join Date
    Dec 2001
    Toronto, Canada
    I know this is going to sound like a dump question, but is Peter creating a permanent or a temporary table CREATE TABLE #mytab? Here a some steps you can to.

    1) When Peter creates this table and then disconnects from SQL Server, when he reconnects does he see the table?

    2) If you connect as SA can you see the table? Do a "SELECT * FROM sysobjects WHERE name = 'mytab'

    3) Have you connected as SA and done a 'sp_helprotect mytab' ?

    From the error message it indicates that the table does not exist, not a lack of permissions.

  4. #4
    Join Date
    Jan 2004
    The issue is repairable with an easy step. The reason why other users will not be able to see this table or select it besides Peter, is because he is the owner of the table. The table should have a owner of dbo. If it does not have dbo owner, which it will probably be owned by Peter, it should be changed to dbo.

    This is how you do it:

    exec sp_chsngeobjectowner '[databasename].[current table owner].[table name]', 'dbo'

    above is how to change the owner of the table. After you do this anyone should be able to see and select from table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts