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