Prima inter pares?

Question

Prima inter pares?

S Hodkinson

SSCertifiable

Points: 7529
More actions
July 10, 2006 at 4:52 am

#173311

We know that a database can have more than one owner but what are the consequences of two or more owners? Who will be 'prima inter pares'(first among equals)? A new upgrade to an application appears to have this facility
Madame Artois

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

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

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

The account with "dbo" wins the db_Owner battle, generally.

When the other db_Owner or sysadmin creates the object, then it can be owned by <Other User>.<database object> instead of dbo.<database object>. Use sp_changedbowner or sp_changeobjectowner to correct this situation.

Be aware that you can have dbo.MyTable and <other owner>.MyTable in the same database...

Andy

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 2

How could you test for who owned what before trouble sets in?

We have already had the situation where deleting a user (when she left) couldn't be achieved as they 'owned' something but we had no idea what. Using sp_changedbowner and sp_changeobjectowner did not help; we simply couldn't not get rid of her. We managed in the end, on a test database, by removing all the users, with the exception of 6-7 users (who owned objects). We then ran sp_validatelogins, which produced no results; however then we could delete these users, leaving just the dbo.

We would hate to have to delete 300-400 users in the Live database, force the leaver out by the above method, then re-insert all the users.

Another scenario they we can think of is, that if an object is owned by <other user> and needs amending, will dbo be able to ?

Madame Artois

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Do you need objects owned by different owners, or are you aiming for the recommended practice of having everything owned by dbo?

The following query will give you a list of all non-dbo objects and their owners:

select name, user_name(uid) from sysobjects where uid <> 1

You will then want to verify that each of these objects doesn't have an object of the same name owned by dbo. After that, use sp_changeobjectowner to change the owner to dbo.

Users with db_owner access should be instructed to qualify names of new objects they create with "dbo." to avoid this issue recurring.

John

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 4

We have tried the script against the relevant database but got no results. When we deleted the element 'where uid <>1' everything came back as being 'dbo'

Madame Artois

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

And there are still users in the database that you can't delete because it says they own objects?

John

S Hodkinson SSCertifiable Points: 7529 More actions · Answer 6

Yes there are. We have tried the script against a test database with no users/owners in and one where we definitely know we have users who own objects with the same results

Am I right in my understanding that these users do not own tables but objects with tables? These particular users are/were data administrators and gave other users access to the application through the application itself. They did not have server access rights under the current version: the new version (upgrade) will give them server access rights.

Our problem is that we have enough trouble already with these data administrators owning objects; we fear the new version will make things worse.

Madame Artois