Understanding Object Ownership

Question

Understanding Object Ownership

Kathi Kellenberger

SSChampion

Points: 11811
More actions
July 5, 2005 at 12:55 pm

#167439

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/understandingobjectownership.asp
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Viewing 15 posts - 1 through 15 (of 19 total)

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

Igor Makedon Ten Centuries Points: 1235 More actions · Answer 1

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?

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

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 Varsha

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

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 Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

N Cook SSC Eights! Points: 981 More actions · Answer 4

Kathi,

Thanks for this article. It is clear and concise.

Nate

Dmytro Andriychenko SSC Enthusiast Points: 181 More actions · Answer 5

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

paulrcyr SSC Enthusiast Points: 192 More actions · Answer 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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 7

Glad I could help. I am thinking about writing a new article on SQL Server 2005 schemas.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

SDM SSCommitted Points: 1581 More actions · Answer 8

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

bill turner-424199 Grasshopper Points: 23 More actions · Answer 9

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

Richlion-690360 Old Hand Points: 357 More actions · Answer 10

Hi All,

I ran across this article and it is nice. Howerver, I am still confused and need a bit of more information - to be honest, I am a bit of a beginner with SQL Server (normally I work with Oracle).

My question is related to the fact that I am asked to revoke access for the developers to login into the SQL Server and use the SA account. They use the Remote Desktop and login with their domain accounts, but I don't think this is a good solution. My question is - can this be avoided?

So I started testing a Client configuration, installed the Client part of MS SQL, so that a developer uses Query Analyzer on his PC, he can login to the databases using his domain account (without using Remote Desktop). I don't know if this is the right way to do it, but I searched and googled for at past two days and I cannot find a short explanation. How should this be done in a Domain environment, where developers should not have access to the server? What are the standards to work with a SQL Server?

I don't suppose we should login from a client as SA or DBO. I use a domain test Windows account - let's call is tom.bear

Now - tom.bear starts the query analyzer and uses Windows authentication. He is a member of db_owner, so he can create a table as DBO:

create table dbo.new_table ( col int)

And this works. But is this the way it should be set up? What is your experience?

Thanks for any suggestions.

(I edited this post because I missed something and it didn't work the first time)

Regards,

Richard

Greg Charles SSC-Forever Points: 45403 More actions · Answer 11

Hi Richard,

Developers in my shop use the client tools (SQL Server Management Studio/Enterprise Manager, Query Analyzer) on their workstations to login to the dev SQL Server using Windows authentication. We (the DBAs) grant them access to the instance and databases.

Developers should never logon as SA because it's the administrative login that can do anything in SQL Server. Likewise nobody but DBAs are members of the Sysadmin server role. We've not found a good reason to make developers DBO or members of db_owner. Normally, they get membership in the fixed database roles db_datareader, db_datawriter, and db_ddladmin(allows dbo. object creation) in the databases they work on.

Greg

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 12

You know, it really depends on the shop -- how job duties are divided. In small shops, sometimes the developer and DBA are the same person.

The best practice would be for developers to ask the DBA to create the new objects and restrict the rights of the developers.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 13

It has nothing wrong for a user to access SQL Server using a domain account if the server is not a production server. You can set permission to his/her account according to your security policy.

Normally, a user can only be a member of application role in a production server.

In either case, a user shoiuld not have SA. Otherwise, our DBAs will lose our jobs.

:hehe:

Richlion-690360 Old Hand Points: 357 More actions · Answer 14

Greg Charles (5/16/2008)
Hi Richard,
Developers in my shop use the client tools (SQL Server Management Studio/Enterprise Manager, Query Analyzer) on their workstations to login to the dev SQL Server using Windows authentication. We (the DBAs) grant them access to the instance and databases.
Developers should never logon as SA because it's the administrative login that can do anything in SQL Server. Likewise nobody but DBAs are members of the Sysadmin server role. We've not found a good reason to make developers DBO or members of db_owner. Normally, they get membership in the fixed database roles db_datareader, db_datawriter, and db_ddladmin(allows dbo. object creation) in the databases they work on.

Thanks Greg,

very valuable info for me.

Regards,

Richard.