dbachecks
dbachecks copied to clipboard
dataplat
New Check - HADR Availability Group Owner
Check that the AG owner is sa. Just got pointed to this article this morning: http://www.cjsommer.com/2016-10-20-who-owns-your-availability-groups/
The example/situation in that post applies to any object in SQL Server...especially databases and SQL Agent jobs. If the login that owns the object is dropped then access to that object can be affected.
I would say this would be more to the point of checking for NULL in the sense of the AG, but whether it is specifically owned by sa will always fail...because you are likely never going to find the owner as sa for the following reasons:
- Security best practice with SQL Server is to disable
saand not use it. - Use of Windows Authentication only means the
saaccount can't be used to create anything. - Unless you scripted out creating the AG, and instead used the wizard, that has to be run as a domain account that has admin privileges in the domain and on the server to create the dependent objects.
What should AG's be set up under? We currently test database ownership is sa I thought, was thinking would be the same here.
Well in most cases you cannot set the owner to sa for the AG because you have to create it under that account. I don't use sa in client environments and we encourage them to disable it and not use it at all.
We can do a test for NULL, as that would mean the original creator of that AG no longer exists on the server or the domain. I just would not tie it down to testing for sa itself.
The problem is when it's NULL the fix is to drop and recreate the AG, would be cool if we could catch it before then.
There is a database owner check already in databases and an invalid owners one? Could they not be used in this context (with a different config if needed)
So looks like the config for the database has both validdbowner and invaliddbowner set to sa to begin with. I can create validhadrowner and invalidhadrowner configs and base tests off that, is that the best approach?
This test hasn't been created/included yet. Not sure on whether you want to leave it open until it is?
A rather good test will be endpoint ownership has to be sa. (Just putting this as an idea if dbachecks does not have one).
@TheRockStarDBA that still goes to my main point, it applies to any object in SQL Server. As well, it will rarely ever be the sa login that owns anything in SQL Server if an environment is configured according to true best practices.
@jpomfret Thankyou -you are absolutely correct and I am a fool :-) The test does need writing (I dont know why I put that reading back now!!)
@TheRockStarDBA I think this would be an excellent set of tests, could you open a new issue for them and we can leave this issue for the HADR ones