owner of db file

Question

owner of db file

sqlguy80

SSCrazy

Points: 2765
More actions
April 20, 2017 at 8:36 pm

#320564

Hi,
I just checked and the owner of db file of a critical db server is blank, when I set it to sa or some sysadmin it doesnt take it and says db is in use.
This is prod so i cant kill connections etc.
Also the db owner of all Databases is some guy who left the company. There is no dbo user but i see the schema.
What is the best practice to change the db owner and file owner to a new login? so i need to put that in sysadmin group?

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

There are two general directions on this. Either set the database owner to sa or set it to a loginless user that has appropriate permissions. Definitely should not be a user who is no longer with the company and definitely should not be blank.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I like setting them to sa. The login can never be removed, and you can't accidentally grant higher permissions to it, since sa is sysadmin.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

How are you attempting to set the database owner, and what is the exact error message that you get? What happens if you run this?
ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa

John

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

sqlguy80 - Thursday, April 20, 2017 8:36 PM
Hi,
I just checked and the owner of db file of a critical db server is blank, when I set it to sa or some sysadmin it doesnt take it and says db is in use.
This is prod so i cant kill connections etc.

How are you changing the owner, via GUI or TSQL

sqlguy80 - Thursday, April 20, 2017 8:36 PM
Also the db owner of all Databases is some guy who left the company. There is no dbo user but i see the schema.
What is the best practice to change the db owner and file owner to a new login? so i need to put that in sysadmin group?

Do you mean a database user for the previous guy is a member of the db_owner role in each of the other databases?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

ricky_lacayo SSC Enthusiast Points: 115 More actions · Answer 5

John Mitchell-245523 - Friday, April 21, 2017 2:20 AM
How are you attempting to set the database owner, and what is the exact error message that you get? What happens if you run this?
ALTER AUTHORIZATION ON DATABASE::MyDatabase TO sa
John

Hi,

It's always a best practice to follow the principle of least privilege. With that said, it's better if the owner is not a regular user that shouldn't have those permissions. SysAdmin account is an alternative to fix this. To change it, i used this:

EXEC sp_changedbowner 'sa'; /*****This si being deprecated thou******/

John Mitchell-245523's suggestion should do it.

Regards
Rick

CC-597066 SSCommitted Points: 1637 More actions · Answer 6

On a related note, there seems to be a security loop hole if the owner of the database is a member of the sysadmin group and TRUSTWORTHY gets set to TRUE on the database so should the owner really be set to a non sysadmin just in case?

https://support.microsoft.com/en-us/help/2183687/guidelines-for-using-the-trustworthy-database-setting-in-sql-server

"If the TRUSTWORTHY setting is set to ON, and if the owner of the database is a member of a group that has administrative credentials, such as the sysadmin group, the database owner may be able to create and to run unsafe assemblies that can compromise the instance of SQL Server."

"Best practices for database ownership and trust include the following:

• Have distinct owners for databases. Not all databases should be owned by the system administrator."

Steve T SSCommitted Points: 1994 More actions · Answer 7

CC-597066 - Thursday, May 4, 2017 4:19 PM
On a related note, there seems to be a security loop hole if the owner of the database is a member of the sysadmin group and TRUSTWORTHY gets set to TRUE on the database so should the owner really be set to a non sysadmin just in case?
https://support.microsoft.com/en-us/help/2183687/guidelines-for-using-the-trustworthy-database-setting-in-sql-server
"If the TRUSTWORTHY setting is set to ON, and if the owner of the database is a member of a group that has administrative credentials, such as the sysadmin group, the database owner may be able to create and to run unsafe assemblies that can compromise the instance of SQL Server."
"Best practices for database ownership and trust include the following:
â€¢ Have distinct owners for databases. Not all databases should be owned by the system administrator."

I disabled "sa" and made it the owner of the database. I didn't see any issue with this. Thoughts?