How to Fix Orphaned SQL Users


Recently I had to involve in a database migration task. I backed up the database and restored successfully in the new server. However when I access the database through the web site, database login didn’t work. The problem was even though the database user is included in the restored database; the login information was not there. So I re-created the login in the server, but it also didn’t work.

This phenomenon is called “orphaned users“.

Database with orphaned users

Details of the Problem:

User logon information is stored in the syslogins table in the master database. By changing servers, or by altering this information by rebuilding or restoring an old version of the master database, the information may be different from when the user database dump was created. If logons do not exist for the users, they will receive an error indicating “Login failed” while attempting to log on to the server. If the user logons do exist, but the SID (secure identifier) in master..syslogins and the sysusers table in the user database differ, the users may have different permissions than expected in the user database. (Microsoft

User login information in syslogins table in the master database

User login information in syslogins table in the master database

User information in the sysusers table in the user database

User information in the sysusers table in the user database

See above example, both tables contain the different SID for the username “eyepax”

How to fix:

1. The easiest way to fix this is delete the user from the restored database and then create and setup the user & corresponding permission to the database.

2. If the user owns a schema in the database, you won’t be able to delete the user. Then you can use the special stored procedure “sp_change_users_login”.


sp_change_users_login [ @Action = ] action
[ , [ @UserNamePattern = ] user]
[ , [ @LoginName = ] login]
[ , [ @Password = ] password]


[@Action =] action

Following are the list of actions can be performed by the procedure.

Value Description
Auto_Fix Links a user entry in the sysusers table in the current database to a login of the same name in sysxlogins. You should check the result from theAuto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When using Auto_Fix, you must specify user and password; login must be NULL. user must be a valid user in the current database.
Report Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
user, login, and password must be NULL or not specified.
Update_One Links the specified user in the current database to login. login must already exist. user and login must be specified. password must be NULL or not specified.

To perform the operations, first you have to select the database which contained the issues. Here are some of the operations you can perform.

  • Lists the orphaned users
EXEC sp_change_users_login 'Report'
Lists the orphaned users

Lists the orphaned users

  • If you already create a SQL server with same login information and if you want to map that with the database user
EXEC sp_change_users_login 'Auto_Fix', 'user'
Auto fix orphaned user

Auto fix orphaned user

3. MSDN says, maps an existing database user to a SQL Server login. sp_change_users_login feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER USER instead.


WITH <set_item> [ ,…n ]

<set_item> ::=
NAME = newUserName
| DEFAULT_SCHEMA = schemaName
| LOGIN = loginName


userName -Specifies the name by which the user is identified inside this database.
LOGIN =loginName, Re-maps a user to another login by changing the user’s Security Identifier (SID) to match the login’s SID.
NAME =newUserName, Specifies the new name for this user. newUserName must not already occur in the current database.
DEFAULT_SCHEMA =schemaName, Specifies the first schema that will be searched by the server when it resolves the names of objects for this user.

Alter user

Alter user

For more information



Troubleshoot Orphaned Users (SQL Server)



SMTP, POP3 and IMAP are TCP/IP protocols used for mail delivery. Email clients use POP3 and IMAP protocol to retrieve email from the server (incoming mail) over TCP/IP connection. Outgoing mail for both POP and IMAP clients uses the SMTP. Main difference between POP3 and IMAP is,

  • POP3 – Downloads email locally
  • IMAP – Mail is stored on the mail server


Simple Mail Transfer Protocol (SMTP) servers handle the sending of your e mail messages to the Internet. The SMTP server handles outgoing e mail, and is used in conjunction with a POP3 or IMAP incoming e mail server.


Post Office Protocol 3 (POP3) servers hold incoming e mail messages until you check your e mail, at which point they’re transferred to your computer. POP3 is the most common account type for personal e mail. Messages are typically deleted from the server when you check your e mail.


Internet Message Access Protocol (IMAP) servers let you work with e mail messages without downloading them to your computer first. You can preview, delete, and organize messages directly on the e mail server, and copies are stored on the server until you choose to delete them. IMAP is commonly used for business e mail accounts.

Default Ports

  • SMTP AUTH: Port 25 or 587 (some ISPs are blocking port 25)
  • SMTP StartTLS Port 587
  • SMTP SSL Port 465
  • POP Port 110
  • POP SSL Port 995
  • IMAP Port 143
  • IMAP SSL Port 993
  • IMAP StartTLS Port 143

The following list of SMTP, POP3, and IMAP server should help you if you don’t know what mail server you should use for your mail account.

  SSL Port 465 SSL Port 995 SSL Port 993
  StartTLS Port 587
TLS/SSL encryption required: yes
Please make sure, that POP3 access is enabled in the account settings. Login to your account and enable POP3. Please make sure, that IMAP access is enabled in the account settings. Login to your account and enable IMAP.
  StartTLS Port 587 SSL Port 995 SSL Port 993
  TLS/SSL encryption required: yes TLS/SSL encryption required: yes  
Yahoo Mail  
  SSL Port 465 SSL Port 995  
Yahoo Mail Plus
  SSL Port 465 SSL Port 995 SSL Port 993
Yahoo UK
  SSL Port 465 SSL Port 995 SSL Port 993
Yahoo Deutschland
  SSL Port 465 SSL Port 995 SSL Port 993
Yahoo AU/NZ
  SSL Port 465 SSL Port 995 SSL Port 993
Windows Live (Hotmail)  
  StartTLS Port 587 SSL Port 995  

JavaScript Frameworks and Resources



Knockout is a JavaScript library that helps you to create rich, responsive display and editor user interfaces with a clean underlying data model. Any time you have sections of UI that update dynamically (e.g., changing depending on the user’s actions or when an external data source changes), KO can help you implement it more simply and maintainably.


AngularJS is an open-source JavaScript framework, maintained by Google, that assists with running what are known as single-page applications. Its goal is to augment browser-based applications with model–view–controller (MVC) capability, in an effort to make both development and testing easier. The library reads in HTML that contains additional custom tag attributes; it then obeys the directives in those custom attributes, and binds input or output parts of the page to a model represented by standard JavaScript variables. The values of those JavaScript variables can be manually set, or retrieved from static or dynamic JSON resources.


Backbone.js gives structure to web applications by providing models with key-value binding and custom events, collections with a rich API of enumerable functions, views with declarative event handling, and connects it all to your existing API over a RESTful JSON interface.


Node.js is a platform built on Chrome’s JavaScript runtime for easily building fast, scalable network applications. Node.js uses an event-driven, non-blocking I/O model that makes it lightweight and efficient, perfect for data-intensive real-time applications that run across distributed devices.


Modernizr is a small JavaScript library that detects the availability of native implementations for next-generation web technologies, i.e. features that stem from the HTML5 and CSS3 specifications. Many of these features are already implemented in at least one major browser (most of them in two or more), and what Modernizr does is, very simply, tell you whether the current browser has this feature natively implemented or not.


RequireJS is a JavaScript file and module loader. It is optimized for in-browser use, but it can be used in other JavaScript environments, like Rhino and Node. Using a modular script loader like RequireJS will improve the speed and quality of your code.


LESS extends CSS with dynamic behavior such as variables, mixins, operations and functions.LESS runs on both the server-side (with Node.js and Rhino) or client-side (modern browsers only).


Sass is an extension of CSS3, adding nested rules, variables, mixins, selector inheritance, and more. It’s translated to well-formatted, standard CSS using the command line tool or a web-framework plugin.

HTML5 Cheat Sheet


HTML5 Cheat Sheet – Tags

HTML5 Cheat Sheet - Tags

HTML5 Cheat Sheet By InMotion Hosting – A Virtual Private Servers Provider

HTML5 Cheat Sheet – Event Handler Attributes

HTML5 Cheat Sheet - Event Handler Attributes

HTML5 Cheat Sheet By InMotion Hosting – A Web Hosting Provider

HTML5 Cheat Sheet – Browser Support

HTML5 Cheat Sheet - Browser Support

HTML5 Cheat Sheet By InMotion Hosting – A Dedicated Server Provider

Online tools for troubleshooting web, SQL, XML and JSON


Have you ever wanted to troubleshoot someone else web page that contains the combination of JavaScript, CSS and HTML? Have you ever wanted to troubleshoot someone else SQL query without entering to the SQL server? Here are some useful online tools that make your life easier.

Following tools provide an online debugging environment for HTML, CSS and JavaScript.

1. jsFiddle

jsFiddle is the most popular JavaScript sandbox. It provides instantly ready coding environment for you to begin experimenting in as soon as the page loads.

JS Fiddle

2. JS Bin

JS Bin is another JavaScript sandbox. The design of this app is quite minimal and allows you to focus on the code. By default, the page only shows the HTML and lives preview panes, but you can easily add JavaScript to that as well.

JS Bin

3. CSSDesk

CSSDesk is a CSS sandbox that allows you to put your HTML and CSS codes there and view the preview instantly. It is very useful for testing out codes before implementing them in your website permanently.

CSS Desk

4. CodePen

CodePen is an app for sharing and playing around with front end code. It’s a site for building stuff from HTML, CSS and JavaScript.

Code Pen

Following tool can use for easy online testing and sharing of database problems and solutions.

1. SQL Fiddle

SQL Fiddle is a tool for easy online testing and sharing of database problems and their solutions. If you do not know SQL or basic database concepts, this site is not going to be very useful to you. However, if you are a database developer, there are a few different use-cases of SQL Fiddle intended for you:

  • You want help with a tricky query, and you’d like to post a question to a Q/A site
  • You want to compare and contrast SQL statements in different database back-ends
  • You do not have a particular database platform readily available, but you would like to see what a given query would look like in that environment

SQL Fiddle

Following tools act as sandbox environment for JSON,  XML development, including (E)XSLT, XML validation (DTD, schema, RelaxNG) and XQuery.

1. XMLPlayground

XMLPlayground is a sandbox environment for XML development, including (E)XSLT, XML validation (DTD, schema, RelaxNG) and XQuery. It has five development panels into which you can either insert or upload code:

  • XML – your source XML. You can render it as a node tree via the ‘tree’ tab.
  • (E)XSLT – transform your XML with XSLT (1.0). EXSLT an XSL includes are supported.
  • Validation – validate your XML with Schema, DTD or RelaxNG.
  • CSS – style your output
  • XQuery – query or transform your XML with XQuery code (via XQIB)

XML Playground

2. JSON Fiddle

With JSON Fiddle, simulating and posting JSON in Fiddler is just as easy.

json fiddle

Counting Consecutive Dates Using SQL


Download demo query

Recently I have answered one of the SQL-based questions in the CodeProject. Thanks for the participant that question wake up me to write this blog post.


He has a table (tblLeave) with the data like below.

5023		SL    	14/12/2012 0:00		14/12/2012 0:00		1
5023		SL    	15/12/2012 0:00		15/12/2012 0:00		1
5023		COF   	16/12/2012 0:00		16/12/2012 0:00		1
5023		SL    	19/12/2012 0:00		19/12/2012 0:00		1
5023		SL    	22/12/2012 0:00		22/12/2012 0:00		1
5023		SL    	23/12/2012 0:00		23/12/2012 0:00		1
5023		SL    	24/12/2012 0:00		24/12/2012 0:00		1
5023		PL    	28/12/2012 0:00		28/12/2012 0:00		1
5023		PL    	29/12/2012 0:00		29/12/2012 0:00		1
5023		PL    	30/12/2012 0:00		30/12/2012 0:00		1
5023		PL    	31/12/2012 0:00		31/12/2012 0:00		1

Moreover, he wants to output the data as below

5023    SL      14/12/2012 0:00 15/12/2012 0:00 2
5023    COF     16/12/2012 0:00 16/12/2012 0:00 1
5023    SL      19/12/2012 0:00 19/12/2012 0:00 1
5023    SL      22/12/2012 0:00 24/12/2012 0:00 3
5023    PL      28/12/2012 0:00 31/12/2012 0:00 4

Condition: If the same type of leave taken continuously, it should be merged in one row mentioning from_date to to_date.


When I saw that question the first time, I did not go through in details and just thought that was an easy grouping query. And I just gave following answer.

FROM tblLeave

However, that is wrong, he made comments saying it does not make sense and highlighted the condition he wants. (Thanks to him; he did not down vote my answer). Again I read the question… Oh… That was a tricky question. He needs to group the leave by consecutive date. Isn’t that tricky?

To answer that, I use the DATEDIFF SQL function

DATEDIFF ( datepart , startdate , enddate )

Following is my answer and the output

FROM tblLeave) AS dt
PAYCODE     LV_TYPE FROM_DATE               TO_DATE                 LVALUE
----------- ------- ----------------------- ----------------------- -----------
5023        SL      2012-12-14 00:00:00.000 2012-12-15 00:00:00.000 2
5023        COF     2012-12-16 00:00:00.000 2012-12-16 00:00:00.000 1
5023        SL      2012-12-19 00:00:00.000 2012-12-19 00:00:00.000 1
5023        SL      2012-12-22 00:00:00.000 2012-12-24 00:00:00.000 3
5023        PL      2012-12-28 00:00:00.000 2012-12-31 00:00:00.000 4

Query Explanation:

Before explaining the logic see the following query and the output.

FROM tblLeave
PAYCODE     LV_TYPE FROM_DATE               ROW_NUMBER           Diff
----------- ------- ----------------------- -------------------- -----------
5023        SL      2012-12-14 00:00:00.000 1                    41254
5023        SL      2012-12-15 00:00:00.000 2                    41254
5023        COF     2012-12-16 00:00:00.000 3                    41254
5023        SL      2012-12-19 00:00:00.000 4                    41256
5023        SL      2012-12-22 00:00:00.000 5                    41258
5023        SL      2012-12-23 00:00:00.000 6                    41258
5023        SL      2012-12-24 00:00:00.000 7                    41258
5023        PL      2012-12-28 00:00:00.000 8                    41261
5023        PL      2012-12-29 00:00:00.000 9                    41261
5023        PL      2012-12-30 00:00:00.000 10                   41261
5023        PL      2012-12-31 00:00:00.000 11                   41261

By seen this, you will realize that above query generate the same Diff  value for all the consecutive dates. Now you can easily group this and get the counts as you like.