Maps database user 'dbo' to a server login instead of sa

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:

1. Human being is not so naive and innocent now-a-day. We couldn't trust people like how it was used to be.
2. There are ways for how applications connect to the database, other than solely connecting as the 'dbo' database user.
3. Based on point 1, the world is asking for more to be done to keep their data secure.
4. 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.
5. 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:

Tracing Deprecated Features SQL Server during database migration or upgrade

Imagine. You have an in-house developed application which you spent so much time and effort on to enable it to close to perfection. Users love you for the state of art build and you feel you have achieved something to be proud of.

2 years down the track, it is time for SQL server major upgrade. Let's bump it up a version and take advantage of the new features and enhancement that Microsoft charge you for. After that hard work on SQL server upgrade, you found that you great perfect application - shows some show stopper issue. You know, it is kind of frustrating if you have developed a database object and one day it just stops working because Microsoft decide to make the world better by retiring existing build-in object and function and replace with a better new one.

Users are asking why your perfect application doesn't work, and suggesting that let's abandon the upgrade project as everything is working at the moment, why change? (the usual normal people...). Time is clicking and you are stress....

I don't want to be there, at that timing. So let's be proactive!! You can found out what deprecated function your application is currently using and how frequent that they are being use with the following query:

And this is a list of link from Microsoft to show you what is required to look out:

Deprecated Database Engine Features in SQL Server 2008 R2

Discontinued Database Engine Functionality in SQL Server 2008 R2

Discontinued Database Engine Functionality in SQL Server 2008

Discontinued Database Engine Functionality in SQL Server 2005

Deprecated Commands in SQL Server 2005

To find out which domain controller you are authenticated to on ther server ?

I was helping my friend in finance to check out some issue in test environment, and of course, which starts as "hey, we got some database issue since we can't connect to to backend...". Great pick up line as usual.

So my friend shows me his database issue, and I can point it out straightaway that it is an authentication plus DNS issue rather than database. It rings the bell that my OP team friend told me about a project that they are looking at upgrading the domain controllers and it is reasonable that they are doing a rehearsal for the big date.

There is 2 active directory servers online and I am not sure which one the application server is authenticated from. Well, I found the following handy environment variable for just the job:

Start / Stop a service at remote Windows server

There is an useful Windows command I learn recently which I thought why I did come across this earlier. It helps a lot when it needs to stop a service on an remote app server prior to run a database script.

The command is sc.

Task	Command syntax
To stop a Service	sc \\{server} stop {service}
To start a Service	sc \\{server} start {service}
To restart a Service	sc \\{server} restart {service}

Event ID to identify a system shutdown, startup, reboot and System uptime

How many times that you are surprised by automated Windows patching task which restarts your database server in the middle of a job runs? Of course, you should discuss that with you system admin team to work out how to schedule and deal with Windows patching on database servers. To start a discussion like that, you always need to find if your server does restart in the first place and what time and by who/what.

So, what are you looking for? In the event viewer, you can filter the system logs with the following Event IDs as explain below:

Event ID	Description
6005 / 6009	OS started
6006	OS shutdown
6013	OS daily update report. that's the OS has been running for more than a day, measure in seconds.
1074	This sweet event shows you which process and on behalf of which user a reboot was initiated.