How to Fix Orphaned SQL Server Users

No, not this kind of Orphan -- too
bad we can't fix this issues as easily!

Just a quick entry this time.  However, it happens all the time: you restore a database to a new server and can't login to the database with the original usernames.  It happens because the usernames have become "Orphaned Users."

It's now easy to fix, as of SQL 2000 sp3.  It used to be REALLY hard before.

First find all your orphaned users, by running this command:

EXEC sp_change_users_login 'Report'

It will yield something like this:

jobean 0x2BB8F8255C002E47B232FB331B1698BA

From this, I know 'jobean' is an orphaned user.

So, run this command to fix it:

EXEC sp_change_users_login 'Auto_Fix', 'jobean'

The row for user 'jobean' will be fixed by updating its login link to a login already in existence.

The number of orphaned users fixed by updating users was 1.

The number of orphaned users fixed by adding new logins and then updating users was 0.

This hooks up the jobean user of the database to the jobean of the database server.  Did this fail (probably because you don't have a jobean user in the database server?)  Then issue this command to make a new user and wire it up to the jobean orphaned user.

EXEC sp_change_users_login 'Auto_Fix', 'jobean', 'login', 'password'

[Where login and password are those you want assigned to the new user]

Easy as can be.

Logging IIS POSTS

Have you ever needed to see the raw POSTS coming into your IIS-hosted web site, but don't want to change code to write it to a file?  Well, there is an easy way:

• Bring up IIS Manager
• Find the Site you want to log, and click on it
• Click the IIS icon, "Failed Request Tracing Rules".  Even though it says failed requests, it also will do successful requests.

• Select "Add..." in the right menu
• Select "All Content (*)" and "Next"

• Then put "200" in the "Status Codes" box.  You could add a range to get all requests: "200-999"

• Then press "Next",  "Finish"

A new folder under your default logs root will be built called "FailedReqLogFiles".   You will see it get filled with XML files that exhaustively log every aspect of all posts.

Remember - Don't leave it on long or it will blast your storage with tens of thousands of files.

IIS Web Deploy ERROR_DESTINATION_NOT_REACHABLE

This is a common error with setting up Web Deploy.  What happens is you install Web Deploy from Microsoft's online link, check that it's a running service, then find it only yields a ERROR_DESTINATION_NOT_REACHABLE error.

Of course, you troubleshoot pinging the remote site.  That's fine.  Then you try telnet to see if the port is available.  It turns out it isn't.  A search of the web yields this (with it's good, but not obvious, clue):

ERROR_DESTINATION_NOT_REACHABLE

Diagnosis: Web Management Service or Remote Agent is not installed or unreachable on the remote computer.

Resolution: Verify that Remote Agent Service or Web Management Service are started on the remote computer, depending on which one you are connecting to. You can do a "net start wmsvc & net start msdepsvc" on the remote computer to ensure these services are started. Also, ensure that a firewall is not interrupting communications with the destination.

The problem is that Web Deploy installs itself, but it doesn't install the Web Management Service.  It also doesn't warn you that it's not available.

So, to install WMS, go to Server => Roles,  then Web Server (IIS) => Management Tools => Managment Service.  Install it...

And Voila!  You're all set!

Restoring SQL Server Database: Exclusive access could not be obtained because the database is in use

Here is the scenario - you are in a rush to restore a down SQL database.  Then you get the dreaded:

Exclusive access could not be obtained because 
the database is in use.

Don't panic.  You can do two things.

Option 1

If you are using the SQL Manager Tool, you select "Options" from the side panel.  On there you will find a checkbox to go into single-user mode for the Restore.  This will do the trick.

If this option is grayed out, cancel the restore operation, then bring it up again.  Check this box first before doing anything else.  It should just work.

Option 2

If you are using the command line, use this command:

USE master
GO
ALTER DATABASE DATABASE_NAME
SET SINGLE_USER

Now do your restore as normal.

Afterward

Don't forget to make sure you are in multi-user mode!

USE master
GO
ALTER DATABASE DATABASE_NAME
SET MULTI_USER

Finding a Dropped Table

I recently needed to find a dumped table that was somewhere in backups, but where??? Obviously, the db stores this in the transaction logs, but its really hard to find if you don't know where to look.

A great query for finding this dropped table is this:

SELECT * FROM fn_dblog (NULL, NULL), (SELECT [Transaction ID] AS [tid] FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] LIKE '%DROPOBJ%') [fd] WHERE [Transaction ID] = [fd].[tid];

This will return back everything you need to get this table off of your backup:

[Current LSN], 3 [Operation], 4 [Context], 5 [Transaction ID], 6 [Description]

Reset Cisco PIX 501 to Factory Settings

I recently had to reset my Cisco PIX 501 to it's factory settings. I had trouble getting the 501's web interface to work. I think more modern web browsers are blocking its access. So, I had to revert to the terminal connection. It wasn't hard, but directions are rather limited, so I decided to write this post.

Steps to Reset to Factory Settings

This is the older version of this cable.  Cisco
now has a newer version, but this is what came with the 501

1. Find the Console Cable (Blue cable with serial connection dongle). This was my biggest problem!
2. Plug the serial side into a old desktop computer, and RJ side into the 501 terminal port
3. In Windows, bring up Hyperterminal.
4. Use the default Hyperterminal settings, 9600, 8, N, 1
5. Once the Cisco shows up within Hyperterminal, go into the configure terminal mode, by typing:
   
   en
   (then enter your password)
   
   Once you get authenicated, you should see the pound sign show up at the command prompt, like this:
   
   CISCOPIX#
6. To get into configuration mode, type:
   
   conf t
7. Finally, to start the process, type:
   
   configure factory-default
8. Wait for the unit to reboot and you should be set with a factory-set 501.

Removing Spaces From SQL Server Data

Have you ever found yourself with spaces in SQL fields that just shouldn’t have spaces, such as usernames.  Well, this is an easy problem to fix with the SQL command "CHARINDEX".  CHARINDEX will return the first occurrence of an expression (such as a space) in a field.

So, you might say:

SELECT CHARINDEX(' ','Rats live on no evil star')

and what is returned is: 5 — the index of the first space in this string (a Palindrome, by the way.)  So, you can use this, along with SUBSTRING, to give you a final string with no spaces in it.  Here is an example:

SELECT UserName,
SUBSTRING(username, 1,CHARINDEX(' ',username)-1)+SUBSTRING(username,CHARINDEX(' ',username)+1,LEN(username)) AS NoSpaceUsername
FROM users
WHERE  CHARINDEX(' ',username)>0

This will yield results, such as: 
UserName       NoSpaceUsername
DE SILVA       DESILVA
DEL REY        DELREY
DEL TORO       DELTORO

I hope this is helpful!