Database Owner Unkown

Question

Post reply

Database Owner Unkown

Djcarpen11

Ten Centuries

Points: 1069
More actions
March 19, 2012 at 4:57 am

#270131

When I run sp_helpdb on one of our servers the owner of one of the databases is ~~UNKNOWN~~, If try to run EXEC sp_changedbowner 'SA' the command never completes and starts to block processes on the database?
How do I fix this issue? Would a detach - reattach and then EXEC sp_changedbowner 'SA' fix my issue?
Dave

Viewing 12 posts - 1 through 11 (of 11 total)

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

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 1

Hi.

What does the following command tell you:

SELECT SUSER_SNAME(owner_sid)

FROM sys.databases

WHERE name = '<your database name here>';

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

rraja Default port Points: 1486 More actions · Answer 2

Djcarpen11 (3/19/2012)
When I run sp_helpdb on one of our servers the owner of one of the databases is ~~UNKNOWN~~, If try to run EXEC sp_changedbowner 'SA' the command never completes and starts to block processes on the database?
How do I fix this issue? Would a detach - reattach and then EXEC sp_changedbowner 'SA' fix my issue?
Dave

have you check which process is blocking your command?

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 3

Thanks for the reply Andre,

I've tried your code and it returns a null value,

Dave

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 4

Djcarpen11 (3/19/2012)
Thanks for the reply Andre,
I've tried your code and it returns a null value,
Dave

I'm not sure what was being blocked as the script was being run by somebody else who noticed the blocking, I obviously don't want to rerun this in production. We've also tried ALTER AUTHORIZATION ON database which also had the same affect.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 5

Have you checked the database for errors with CHECKDB?

There seems to be something very wrong there.

I heard about an issue like that before but it was easily fixed using 'sp_changedbowner'.

EDIT: Or even setting the database to single-user before trying the command above?

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Djcarpen11 Ten Centuries Points: 1069 More actions · Answer 6

I was panning on setting it into single user mode tonight and running the command, I'll try a restore of DB and run checkDB against that this afternoon to see what it brings back.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

You are not blocking anyone by trying to run the command to change the DB owner, the change-owner command itself is being blocked. You cannot change the owner of the DB unless you have exclusive access to the DB.

Here's what you need to do:

1. Change your own DB context to the DB in question (i.e. USE db_name). This step is important to guarantee exclusive access.

2. Set the DB to SINGLE_USER mode.

3. Change the DB owner using ALTER AUTHORIZATION (sp_changedbowner is deprecated)

4. Set the DB to MULTI_USER mode.

I see this problem a lot. When creating a new database the owner is automatically set to the user who created the database. Chances are whoever created the database did not know this or did not bother to change the owner to something generic. That person may no longer be a part of the organization and their SQL Login or Active Directory login may have since been deleted. This leaves the SID that owns the database unresolvable. This scenario generally has little to no impact of SQL Server's ability to host the DB, however it is annoying when trying to view DB properties through SSMS, and it can cause various issues with security (e.g. with cross-DB ownership chaining if you use that) so it's a good idea to clean it up. I set all my DB owners to sa, since there is no way for anyone to delete that account, and it works as expected even if the sa account is renamed or disabled.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

NaCl SSC Journeyman Points: 80 More actions · Answer 8

not so sure i agree with setting all DB owners to SA.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

NaCl (4/27/2012)
not so sure i agree with setting all DB owners to SA.

This is a discussion Forum, all opinions are welcome. Care to share why not?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

NaCl SSC Journeyman Points: 80 More actions · Answer 10

from Microsoft's 2008 R2 Security Best Practices Whitepaper:

•Have distinct owners for databases; not all databases should be owned by sa

i understand the advantage of ownership by an account that can't be deleted but i tend to lean towards MS on this though. In my world most of our instances have multiple databases which makes our auditors twitchy. By having a distinct owner for each DB we reduce the risk of cross access and our auditors are less twitchy. We also don't create jobs or set maintenance plans as sa. And then the last piece, which sort of takes care of itself, is run sp_SetAutoSAPasswordAndDisable so no one can login as sa. Instead of a common generic account each DBA/Admin has their own login ID with the proper authority. This has worked beautifully when reviewing activity logs.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 11

NaCl (4/30/2012)
from Microsoft's 2008 R2 Security Best Practices Whitepaper:
•Have distinct owners for databases; not all databases should be owned by sa
i understand the advantage of ownership by an account that can't be deleted but i tend to lean towards MS on this though. In my world most of our instances have multiple databases which makes our auditors twitchy.

I can definitely understand wanting to keep auditors from getting twitchy.

By having a distinct owner for each DB we reduce the risk of cross access ...

How so? ...I guess I diverge from the Best Practices document in this area. I can see one area where they're protecting us...a database becoming TRUSTWORTHY after the initial setup without proper thought given to all the implications, however in systems relying on the TRUSTWORTHY bit we have a lot more to worry about than just who is the database owner. Where I am coming from is that for a base installation there is little risk in having sa own all databases, and it adds a level of consistency and convenience, as well as the noted protection from the owners disappearing from the system. Please feel free to pile on more reasons not to use sa, but at present I am not convinced of any risk, perceived or real.

We also don't create jobs or set maintenance plans as sa.

I would be more apt to give up having all jobs owned by sa well before giving up sa as the database owner for all databases, mainly due to the context in which SQL Agent runs processes for jobs owned by sa. But again, sa as job owner for all jobs is my base installation and I adjust as required, which has not been often.

And then the last piece, which sort of takes care of itself, is run sp_SetAutoSAPasswordAndDisable so no one can login as sa.

In my world sa is disabled as well. sa can be disabled and still the instance is quite happy with the login owning any or all databases or jobs.

edit: expand 'dbo' as 'database owner' for clarity

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato