Fixing Orphaned Database Users in SQL Server 2005

I see this on almost all SQL Server instances I work on - existing user in a database with dbo permissions yet they could not access anything in the database. This is because SQL Server doesn't really look at the user or login name but rather the SID values (I've talked about this in here). Maybe they restored a database back from a development server to the production server without synchronizing the logins. If this was SQL Server 2000, you'd probably end up dropping logins, recreating the logins using the script provided by Microsoft in this KB article and recreate the database users. Unfortunately, this is not a straight-forward task. Good thing Microsoft has provided the sp_change_users_login stored procedure to fix orphaned users. Using the UPDATE_ONE parameter will 're-map' an orphaned database user to an existing SQL Server login with the same name. That in itself will spare you the trouble of going thru the process mentioned above (although you will still have to worry about orphaned database users that own schemas which is very much common among databases that have been upgraded from SQL Server 7.0 and 2000). Below is a syntax for 're-mapping' an orphaned database user to an existing SQL Server login

EXEC sp_change_users_login 'UPDATE_ONE','user','user'
GO

You still need to run the stored procedure with the REPORT parameter to return a list of orphaned database user

EXEC sp_change_users_login 'Report'

Whacked out Windows Service

I was supposed to blog about this sometime last year when we we're called in to be standby engineers for a large government organization in Singapore as they were trying to change their hardware for one of their SQL Server clusters (I had another similar incident today which reminded me to blog about it). When they didn't get the chance to successfully move to the new hardware, they rolled back their changes. Unfortunately, even their rollback process was screwed up as they couldn't bring up the services after doing a restore of their backup. Looking at the services applet in Windows, we couldn't start the service at all. It looks like their backup software was not able to resolve the long names in Windows, thus, introducing the tilde (~) character in the location of the executable for the service. The workaround for this is a registry hack. You can remove or change settings in your service by looking at this registry key

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

Look for the service name and update the ImagePath key to the correct parameters that you would need for your service. In case you're thinking of deleting a service when it was not properly uninstalled or when it is totally screwed up, you can use the Windows command line tool sc to do the trick

RELOG tool in Windows Server 2008

I was configuring one of our customer's Windows Server 2008 to capture PerfMon counters for our analysis when I accidentally ended up storing the log file in binary format. Now, I didn't like the idea of reading the binary file as I will be importing it in a SQL Server database for Reporting Services to use. Although PerfMon has the ability to log directly to a SQL Server database using ODBC, I am not allowed to do anything outside of PerfMon. After digging thru some options in Windows Server 2008, I found out about the RELOG tool. The tool gives you the ability to extract out the counters from any format generated by PerfMon and store them in a different format. In my case, I had to store them as a CSV file



relog PerfMonCounterLog.blg -f csv -o PerfMonCounterLog.csv



I was trying to find more about this tool from Microsoft TechNet but couldn't find any (or maybe I wasn't just looking hard enough) although MSDN has something about the method called ReLog which does exactly the same thing. It's exposed as an API so you can call it from an application should you wish to do so

A thousand and one reasons for a generic error message.

I've spent a couple of hours trying to troubleshoot a clustered SQL Server 2008 installation. All I know is that it throws this error message after the installation process and does not give me any clue at all

The cluster resource ‘SQL Server (MSSQLSERVER)’ could not be brought online. Error: The group or resource is not in the correct state to perform the requested operation. (Exception from HRESULT: 0×8007139F)

Now, this might look like a dependency issue not working correctly but when I checked the Failover Cluster Management console on Windows Server 2008, all the dependencies are online and working as expected. As always, I started searching the Internet for related errors and couldn't find anything really specific except for the same thing - dependency issue. Now, here's what I found out. Since all of the dependencies - disks, MSDTC, IP and virtual server name - are all online, maybe it doesn't have anything to do with them after all. So the first thing I did was to do a PING test to the virtual server name for my clustered SQL Server instance and guess what I found out - there is another IP registered on the DNS server with the same FQDN (maybe a previous installation that wasn't cleaned up properly). I logged in to the DNS server and updated the IP address of my clustered SQL Server instance, ran ipconfig /flushdns on the node on which I am logged in and started the service in Failover Cluster Management. It worked! It just tells you that you should think outside of the box every now and then. It really pays to have that background in network and systems infrastructure every once in a while.