Once upon a time, when human being was so naive and innocent, everyone wrote their application starting with the statement:
"It needs to connect to the database as the 'dbo' database user, or the application would not work at all."That was the golden rule of thumb in software development. Years and eras gone by and a bunch of developers keep telling the whole world that it is necessary to stay that way, or the application would not work at all. Full stop.... The bunch of developers said.
Let's face the now-a-day facts:
- Human being is not so naive and innocent now-a-day. We couldn't trust people like how it was used to be.
- There are ways for how applications connect to the database, other than solely connecting as the 'dbo' database user.
- Based on point 1, the world is asking for more to be done to keep their data secure.
- Based on point 2, if one could not code without solely connecting as the user 'dbo', it is more likely an issue with capability or indolence.
- Based on point 1 & 2, the argument that "an application required to be the DBO user and the database owners" is simply invalid.
Given there are now-a-day facts, there are still out-dated people thus applications. In our case, we have limited choices for a number of reason to stuck with a suboptimal application. At certain point, you can only work with it.
As a DBA, one would usually find that this happens when a database needs to be migrated from an old SQL Server 2000 instance to a newer version on SQL server. Once you restore the database and found that the database owner was empty, in general a DBA will assign the database ownership to 'SA' and then create a database user for the App's service account - here is the point that when the application vendor would told you that it is not 'correctly' set up.
What you can do to compromise the database to comply with their so called system requirement? The following command would help you to associate a server login to the database dbo user:
