Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There are a bunch of databases on one of our SQL servers that have no owner. Generally speaking, is it harmful to give them one? For example:

USE dbName;
GO

EXEC sp_changedbowner 'sa';
GO

I know sa may not be the best choice, but it is just an example. My primary concern is whether or not creating an owner when there is none may cause issues with software that can currently connect OK.

share|improve this question

3 Answers 3

up vote 7 down vote accepted

You should be using DDL instead of backward compatibility stored procedures:

ALTER AUTHORIZATION ON DATABASE::dbName TO sa;

And the owner of the database (never mind sa) should probably not be the account that your applications use, so this should not really have any effect on your applications. If it does then you should update the connection strings your application uses (which shouldn't be a major change) and make sure the account has sufficient - but not God - privileges.

share|improve this answer
    
Thank you for the advice. However, " My primary concern is whether or not creating an owner when there is none may cause issues with software that can currently connect OK." –  Eric Burcham Mar 19 '13 at 23:34
    
@Eric the problem is there is a difference between "none" and "invalid" - why do you think there is NO owner? –  Aaron Bertrand Mar 19 '13 at 23:37
    
I think you're right. I noticed the issue with Right-Click-->Properties and got the common "Cannot show requested dialog. Property Owner is not available for Database 'dbName'. This property may not exist for this object, or may not be retrievable due to insufficient access rights." But I just ran a query: select name, owner_sid from sys.databases. I did get an sid for the database, but when I use SUSER_SNAME(owner_sid) instead, I get a NULL in the 2nd column. So, invalid owner, correct? Thanks! –  Eric Burcham Mar 19 '13 at 23:48
    
@Eric yes, correct. –  Aaron Bertrand Mar 19 '13 at 23:49
1  
No, there is always an owner (it just may not be valid). What your situation means is that it is owned by a login that exists on the old server but has not been migrated to the new server. Just set them all to sa and be done with it unless you need those logins for other reasons (however if the login is not crucial to exist on the current server in the first place, it's very questionable whether it should have ever owned the database, never mind should own it now that it's been moved). –  Aaron Bertrand Mar 20 '13 at 0:12

By declaring a database owner, you give that user total control over a database. Database owners have full access to all data within all tables, be able to CREATE/ALTER/DROP objects within the database, etc. This can be good or bad, depending on your requirements. Typically, I set all database owners to 'sa' to provide a default owner. As 'sa' already has sysadmin rights, granting database ownership doesn't grant escalated security.

share|improve this answer
    
So I'm hearing that adding an owner to a database without one only ADDS access rights to the database. It will not damage any existing rights. Is that correct? This seems trivially obvious. However, I'd rather really understand this than be sorry. –  Eric Burcham Mar 19 '13 at 23:35
    
@Eric if the current owner is invalid then it can't possibly be being used by your application. Where did the database come from? –  Aaron Bertrand Mar 19 '13 at 23:51

I suggest that always give an owner to database. Since some tools complains when database does not have an owner, for example diagram tool.

Diagram Tool, owner complain image Also using already powerful user like "sa" for this purpose is better like Mike Fal suggested.

share|improve this answer
    
There is a difference between NO OWNER and INVALID OWNER. This isn't a case of NO OWNER. –  Aaron Bertrand Mar 20 '13 at 0:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.