Changing DB Owner

Question

Changing DB Owner

Dennis-214270

SSC Veteran

Points: 291
More actions
March 7, 2005 at 8:50 am

#164804

I want to drop a Windows Authenticated user from a database, but the NT user shows as DB Owner when I look at the properties of the database.
If I change the DB Owner from this NT account to sa (changedbowner 'sa')
Do I also have to look in the database to make sure that this NT account doesn't own an objects within the Database? If so, what is the command to do this?

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

Yes you should even though it is a bad practice (everything should be owned by dbo) I would check and to change that you need

sp_changeobjectowner 'objname', 'user/role' -- preferably a role

Cheers,

* Noel

Jpotucek SSCoach Points: 18583 More actions · Answer 2

Sorry if I seem ignorant, but what is a bad practice?

noeld SSC Guru Points: 96590 More actions · Answer 3

>> Sorry if I seem ignorant, but what is a bad practice? <<

To have owners on objects other than dbo.

I would like to stress though that there are very very ... did I said very limited cases in which it makes sense to have users owned objects

HTH

* Noel

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

This depends... sometimes you want objects not owned by dbo, especially if dealing with cross-database ownership chaining when you want to break the chain.

Also, before forcing an object owner change, make sure you script out the permissions. Changing the object owner will immediately drop any permissions assigned to the object.

K. Brian Kelley
@kbriankelley

noeld SSC Guru Points: 96590 More actions · Answer 5

>>This depends... sometimes you want objects not owned by dbo, especially if dealing with cross-database ownership chaining when you want to break the chain.<<

OK on a percentage basis what percent do you assign to those "sometimes" cases -- My answer: 1% to 2%

You my solve DB ownership chaining and you may break object ownership chaining

* Noel

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 6

I'd put the percentage slightly higher, but not by much. However, the argument can be made from a security perspective not to use dbo owned objects, period. You still maintain ownership chaining if the objects are owned by the same user, just not dbo. Also, it'll ensure developers are explicitly declaring the owner of said objects, thereby eliminating a performance concern.

K. Brian Kelley
@kbriankelley

noeld SSC Guru Points: 96590 More actions · Answer 7

>> Also, it'll ensure developers are explicitly declaring the owner of said objects, thereby eliminating a performance concern<<

And this one is another Herculian task

* Noel

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 8

Be nice to developers! Sometimes I am one.

K. Brian Kelley
@kbriankelley

noeld SSC Guru Points: 96590 More actions · Answer 9

>>Be nice to developers! Sometimes I am one<<

Me TOO

* Noel

Dennis-214270 SSC Veteran Points: 291 More actions · Answer 10

so.... Once again excuse my ignorance..... but what is a quick way to see what objects a user owns before dropping the user??

noeld SSC Guru Points: 96590 More actions · Answer 11

exec sp_helprotect @username = 'YOURUSER'

btw you should be using roles instead of users

HTH

* Noel

Johnny Ed Old Hand Points: 326 More actions · Answer 12

Im interested in the reasons why one might change database

ownership to SA for each non system database (e.g. away from local administration account or other domain user owners to SA) using sp_changedbowner, if objects are owned byd DBO.

Ive read somewhere that the database owner should be SA and users should be granted the db_owner rights where possible.

This sounds like it could be a nightmare to implement and am now thinking that perhaps I shouldnt start changing database ownership for the handfull of client applications (even though most db objects are owned by dbo).

I would have thought that if the origianl database owner (before change to SA) also had the db_owner database role granted to them, then there would be no difference - is this correct ?

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 13

1) If the database owner is something other than sa, say it's a Windows account, then that person can do anything he/she wants on the database. For instance, once upon a time I was a web developer. I developed a database for an intranet application, created a backup, and the DBAs restored the backup to the production server (which I had login capabilities to). When that database was restored, I was the database owner. Meaning as a developer I had the ability to change the database running in production. Not good and I quickly pointed this out to the DBAs.

2) As long as you aren't crossing databases, who the owner is doesn't make a whole lot of different except in the scenario I posed above. Whether the database is owned by YourDomain\JohnDoe or sa doesn't matter to YourDomain\SuzieQ so long as YourDomain\SuzieQ has the appropriate rights to do her job. However, if you are crossing databases and you've got cross-database ownership chaining on, having the databases owned by the same login (such as sa) has security implications because that means an ownership can be established for the dbo objects. After all, dbo maps to login and the login is the owner across the databases. As a result, this would bypass the security check if the databases are configured for cross-database ownership chaining.

K. Brian Kelley
@kbriankelley

Johnny Ed Old Hand Points: 326 More actions · Answer 14

Thanks - thas exactly what I was after.

Is there any benefit in changing the database ownership to SA over and above say the SQL Server service account (e.g. "YourDomain/SQL Server Service Instance Cx2" ).

Speaking of which, what SQL Server permissions/fixed roles (not windows) does a SQL Server or SQL Agent service a/c explicitly require - perhaps this is the answer to my question ?