When databases are created, the owner defaults to whoever created it. This ownership gives the creator additional permissions, and this can be a problem in a locked-down secure environment where we need to honor the principle of least privilege.
This part of our SQL Server sp_Blitz script checks sys.databases looking at the owners. It was contributed by Ali Razeghi.
To Fix the Problem
If you want to make SA the owner on a database, run this T-SQL command:
ALTER AUTHORIZATION ON DATABASE::ReplaceThisWithYourDatabaseName to sa;
After the change, your server will be more secure – but users may be accustomed to doing things they no longer have permission to do. Check with the former database owner to make sure everything continues to work.
Hi Brent, I looked over the script – very impressive. I was wondering why the groups for SQL Server Agent and SQL Server Service SID is not being filtered by your analysis script. Don’t these groups require the sysadmin server role?
Michael
Michael – thanks, glad you like the script! Those groups do indeed require sysadmin, but that’s not what I’m checking here. This check looks at the database owner. The database owner should be SA. I wouldn’t use the Agent or service accounts for this.
Hi Brent; when I first ran Blitz and I realized I had lazily created some databases where owner sa; I immediately executed sp_changedbowner to fix the problem. I then read this page and noticed that another option was ‘Alter Authorization on database’. Is there a reason why you chose one over the other; or is it just two means to the same end?
Thanks,
Bob McC
Bob – off the top of my head, no, they should both work fine.