Quick Scan Report – Database Ownership

Who owns that database?

A quick check of the owner of most databases will likely reveal that the databases are either owned by sa, or by someone with syadmin (or domain admin permissions). This is not recommended, however it is typically the case.

Warning: there are some applications that require the database to be owned by sa, or a user with sysadmin permissions. If you are going to change the database owner, be sure to test it and have a plan to change the owner back if you need to.

Changing the ownership of the SSISDB to anything besides sa is a bad idea. Changing the ownership of SSISDB may prevent future SQL Server upgrades from completing.

It is possible for a non-admin to get security escalation using a database that is owned by sa, even if the sa account is disabled. There is a really great article written by Raul Gonzales that describes exactly how to do this. http://www.sqlservercentral.com/articles/Security/121178/

Suggestions:

  • One login for each database
  • One login for each SQL Servers
  • One login for all your SQL Servers

These logins can be either domain (Active Directory) or SQL Logins, but they should not have any special permissions, and the accounts should be disabled for login, and connections not allowed.

I like to use an account called that describes that it is the database owner, something like DBOwner with an extremely complex password that will never be used, CONNECT denied, and LOGIN disabled. Something like this.

USE [master];
GO

CREATE LOGIN [DBOwner] 
	   WITH PASSWORD=N'SomeReallyGoodPassword', 
	   DEFAULT_DATABASE=[master], 
	   CHECK_EXPIRATION=OFF, 
	   CHECK_POLICY=ON;

GO
DENY CONNECT SQL TO [DBOwner];
GO
ALTER LOGIN [DBOwner] DISABLE;
GO

Here is TSQL Code to generate TQL scripts to change the database owners. Just copy and paste from the Script column, review the script and then you can run it to change the database owner.

SELECT suser_sname( owner_sid ) as [UserName], 
  	   IS_SRVROLEMEMBER('sysadmin', suser_sname( owner_sid )) as [IsSysAdmin],
	   name, 
	   database_id,
	   'ALTER AUTHORIZATION ON DATABASE::[' + name + '] to DBOwner;' as Script
FROM sys.databases
WHERE database_id > 4
  AND name <> 'SSISDB'