Database Owner? What user owns your database?

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 sysadmin permissions (or domain admin permissions). This is not recommended, however it is typically the case. It is generally recommended that the owner of a database be a user with limited permissions, certainly not sysadmin, sa, local admininstrator, or domain admin permissions.

If you database is set to TRUSTWORTHY ON, then 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/

If the database is not set to TRUSTWORTHY ON, if any non sysadmin user has permissions to modify stored procedures, they could always add the SET TRUSTWORTHY ON for a database in a stored procedure that gets run by anyone (or any process) that is running with sysadmin permissions.

Suggestions:

• One login for each database
• One login for each SQL Server
• 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 owner. 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

See Also:

• Database Health Monitor – Quick Scan Report
• Database Health Monitor

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!