Database owner set to SA - SQL 2005/2008

Question

Database owner set to SA - SQL 2005/2008

SQL_Student

SSCrazy

Points: 2536
More actions
June 6, 2011 at 3:24 am

#240881

Good Day all
Please assist, I've come accross few articles saying sa login should be disabled on SQL servers. I'm going through an exercise changing all db owners and wanted to change them to sa but this seems like not a good thing to do in SQL 2005/8.
I just like to know even though the setting cross-database ownership chains is off is it still not advisable to change the owner of db to sa.

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Why does it seem like it's not a good thing?

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

SQL_Student SSCrazy Points: 2536 More actions · Answer 2

Well from what I read, because sa is a widely used SQL admin account, there's possiblities of it being hacked easily. Well would you advise changing owner to sa. It was also mentioned that the sa accounts should be disabled. Please advise further

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Ok, you're talking about two different things here.

1) The sa account is well known and hence the account should be disabled in all SQL 2005+ servers.

2) To avoid unplanned escalation of privileges (and odd things breaking) the owner of all databases should be sa.

Both are standard recommendations. Which do you have a concern about?

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

SQL_Student SSCrazy Points: 2536 More actions · Answer 4

So sorry but confusion has struck, So db owners can be sa but account disabled, but wont disabling the sa account render objects owned by sa unsable.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Did you try and see what happened?

For ownership SQL doesn't care in the slightest if the account is enabled, disabled, locked out, etc. It just has to be a valid login (which is why domain logins are not recommended, if they are disabled in AD, they are no longer valid logins)

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

SQL_Student SSCrazy Points: 2536 More actions · Answer 6

Thanks alot, havn't tried it as yet will do though, as there is no developement enviro here only production. Rather gather enough info before I break something. 🙂

Thanks again

SQL_Student SSCrazy Points: 2536 More actions · Answer 7

Sorry dude one last thing, I came across this document: SQL Sever 2005 security best practices - operative and administrative tasks

which mentions under Database ownership and trust that not all databases should be owned by SA, would you or anybody else know why is this the case

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

With no idea of what document you're talking about or the context in which it makes that recommendation, no.

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

SQL_Student SSCrazy Points: 2536 More actions · Answer 9

thanks again

fyi

in case you wondering

http://download.microsoft.com/download/.../SQL2005SecBestPract.doc

EdVassie SSC Guru Points: 60531 More actions · Answer 10

FWIW, FineBuild will create a low privilige SQL Login to be the database owner account.

The account is marked as disabled. A job is created to run once a week and change ownership of any user databases owned by an account with Sysadmin rights so it is owned by the database owner account. The System databases are not changed.

I cannot see any advantage in having a user database owned by an account with Sysadmin rights (including SA), and assigning ownership to a low privilege account minimises the possibility of privilege escalation. The FineBuild documentation also says that each set of databases connected by an ownership chain should be assigned to a separate database owner account.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara