Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Understanding Object Ownership Expand / Collapse
Author
Message
Posted Tuesday, July 5, 2005 12:55 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:10 AM
Points: 769, Visits: 244
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/understandingobjectownership.asp

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #197104
Posted Monday, July 18, 2005 6:07 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 28, 2013 11:40 AM
Points: 45, Visits: 79

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?

 

Post #201976
Posted Monday, July 18, 2005 9:02 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 12:24 PM
Points: 3,475, Visits: 579

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.




Regards,
Yelena Varshal

Post #202045
Posted Monday, July 18, 2005 12:28 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:10 AM
Points: 769, Visits: 244

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.



Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #202129
Posted Wednesday, July 20, 2005 7:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 5:54 PM
Points: 99, Visits: 22

Kathi,

Thanks for this article. It is clear and concise.

Nate

Post #202742
Posted Friday, January 5, 2007 5:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 27, 2007 7:31 AM
Points: 81, Visits: 2

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

Post #334620
Posted Saturday, January 6, 2007 8:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 28, 2010 4:06 PM
Points: 96, Visits: 6

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




Post #334877
Posted Monday, January 8, 2007 8:31 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 7:10 AM
Points: 769, Visits: 244
Glad I could help.  I am thinking about writing a new article on SQL Server 2005 schemas.

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #335079
Posted Wednesday, January 10, 2007 10:32 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, June 5, 2014 8:23 PM
Points: 363, Visits: 358

Kathi,

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




Post #336067
Posted Thursday, March 29, 2007 12:00 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2008 1:46 PM
Points: 1, Visits: 3
From the title of this article, I expected a discussion of various ownership strategies included in the article, the pros and cons of having dbo as the owner, as well as when it appropriate to have some other owner.

Bill
Post #354852
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse