Permissions and DBO question

Question

Permissions and DBO question

Jpotucek

SSCoach

Points: 18583
More actions
March 2, 2005 at 6:19 am

#87736

I have several Production Databases on my SQL Server 2000 Standard Edition Server (mixed mode authentication) that I'd like to change DBO Permissions on. When I look at the Properties of the DB(s) from EM, the Database owner is showing up as a Windows NT USer who is no longer even with the company!!
Can I use the sp_changedbowner 'sa' command to change the database owner to sa without disrupting Production? Might sound like a dumb question.. but ya never know!!!!!

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

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

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 1

In theory yes you can change it to sa. But on my oppinion it would be better to change to a existing domain login, who have sa rights. Sa as db owner sounds very unusuall

Jpotucek SSCoach Points: 18583 More actions · Answer 2

Thanks much!! I guess I don't understand why sa as db owner sounds unusual. .. but changing them to another domain login sounds like the thing to do. I'm just thinking that if they delete the NT account that owns the Database.. that could be trouble! a couple of Developers and a DBA or two who are no longer here have DB Owner from their Domain accounts. I guess not a good thing?

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 3

If they doesn't work for your company so why their logins are still in your SQL server?? You should change the dbowner and other objects, where they are dbo, owner and drop their accounts.

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 4

Some more additional information:

You can drop them from node (where SQL server reside) local NT group. A couple minutes ago I tested that. Than I successfully changed dbowner from non existing local admin account to other.

You can do that if you are one of the NT local admin group, or if you have security administrator rights on a SQL server

Good Luck!

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

Changing to another domain account (unless it's the SQL Server service account) runs the risk of having the same issue again. I'm not sure you gain anything by using anything other than sa, especially if you're going to use another sysadmin member. The owner of the database makes a difference when you have to restore a database and for whatever reason SQL Server can't look inside the database to find out who the members of the db_backupoperator fixed role are AND you aren't using a sysadmin level account to do the restore. Key point there is you aren't using a sysadmin level account. It also makes a difference in cross-database ownership chaining, but if you aren't using that, it isn't a concern.

K. Brian Kelley
@kbriankelley

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 6

It looks like it sounded unusuall only for me I think that there is nothing bad if DB owner would be a member of local admin group or a member of a domain admin group. But yes, I must agree with you, that after some time there could be a risk of having the same situation again.

Ok, I'll think about that in the future. Thanks bkelley for your remark

Jpotucek SSCoach Points: 18583 More actions · Answer 7

Thank you one and all. I was a little leary about changing the DB owner to another Domain Account - even one that is a member of the local admin group and one that starts the Services. I kinda walked into a mess here and I have Databases 'owned' by 3 or 4 people that arent' here anymore!! I guess I would have a bigger mess when they finally decide to delete the NT accounts

I think I'll stick with sa. Thanks much!!!!!