Database owner and permissions of the owner

Everything in database world must to have an owner. This is specially true regarding database itself. The owner of database has all available permissions in the database. Granting new permissions for database owner in the database does not make sense.

Changing the owner on database results in permissions to be changed, so usually if dba decides to change the ownership from LoginA to LoginB and LoginA should still have some a
ccess to the database, those permissions would need to be granted separately.

Let's suppose we have a database ALFA. We also have a server login AlfaApp. To create the login following command could be executed:

CREATE LOGIN AlfaApp WITH PASSWORD 's3cr3t5!'

To verify permissions of that user on the database try to start on behalf of the user USE statement:

[sap@localhost ~]$ isql -S192.168.2.11:5000 -UAlfaApp -Ps3cr3t5!
1> USE ALFA
2> GO
Msg 10351, Level 14, State 1:
Server 'SAP01', Line 1:
Server user id 6 is not a valid user in database 'ALFA'
1>

The error message is clear - user AlfaApp cannot access ALFA database. Who is the owner of database? This information can be displayed using sp_helpdb:

exec sp_helpdb ALFA

Result could be like this:

Here "sa" is mentioned as database owner. Owner can be easily changed. As administrator change your current database to the modified database and execute sp_changedbowner like in this example:

USE ALFA
GO
EXEC sp_changedbowner AlfaApp
GO
EXEC sp_helpdb ALFA
GO

Be warned - new owner cannot have user associated in the database! If the user matching that login already exists, command will fail.
The last command displays information regarding database after modification:

as you can see - the new owner is AlfaApp! And what about permissions? Without any additional action on administrator side, the user just has all the permissions - just continue the previously failed attempt of changing current database. This time it will succeed:

[sap@localhost ~]$ isql -S192.168.2.11:5000 -UAlfaApp -Ps3cr3t5!
1> USE ALFA
2> GO
Msg 10351, Level 14, State 1:
Server 'SAP01', Line 1:
Server user id 6 is not a valid user in database 'ALFA'
1> USE ALFA
2> GO
1> SELECT DB_NAME()
2> GO

 ------------------------------------------------------------
 ALFA

(1 row affected)
1>

No surprise, that changing ownership on database, changes also permissions, what can cause a lot of incidents with application or user loosing access to it's data.