In an article like this the definition of dbo is a good thing to explain. BOL is vague about it: "The dbo is a user...". Does it mean a login or a server role, or anything else?
Hello,
This is a good research work, thanks.
I would also add that when a database is restored / moved from another server the db owner changes in most cases unless it is SA or Domain Login with access to both machines or a standard login explicitly created with the same SID on both machines (the list of exceptions is not possibly complete). We did see ownership issues in this case because DBO user is not mapped to any login which is not expected by most of developers if they move databases themselves in the development environment.
Also many beginners do not realize that by default SQL EM (Enterprise Manager) has a local server registered in the security context of Windows authentication. So is the default selection when registering the servers. So in many cases when they create objects it is thire Windows login that is the owner.
Yelena.
DBO is a database user. There are no special rights on the server and it isn't a login. There is also a role called db_owner that DBO is a member of. Members of this role can do anything within a database.
Kathi,
Thanks for this article. It is clear and concise.
Nate
Cannot agree more, nice and handy.
I just wanted to say that it is neater to use EXEC in front of the stored procedure rather than GO at the end, so that the code may look like this:
declare @OldOwner varchar(100)declare @NewOwner varchar(100)
set @OldOwner = 'sys'set @NewOwner = 'dbo'
select 'EXEC sp_changeobjectowner ''[' + table_schema + '].[' + table_name + ']'', ''' + @NewOwner + '''' from information_schema.tables where Table_schema = @OldOwner
and the output may look like this:
EXEC sp_changeobjectowner '[sys].[author]', 'dbo'EXEC sp_changeobjectowner '[sys].[OrderItem]', 'dbo'
Just a very minor thing, thank you for the script. I used to do this using a cursor and PRINT command but it is much quicker this way.
Thank you,
Dmytro
Object Ownership has been something that has confused me for some time but now it is all clear. Thanks for submitting the article it was very easy to understand especially the chart!!!
Paul Cyr
Thanks for the article, and looking forward to the 2005 version. We've just started investigating the implications of the user / schema separation, and oooohhh.. there seem to be a few gotchas.
Stephen
