Prefix 'N' for Unicode values in SQL Server

Chances are that you have a nvarchar, nchar or ntext field in SQL Server which expects a Unicode value. However, when you INSERT/UPDATE the field, you literally end up having ????????????? in the field. So what went wrong.

It is important to understand that any n... field expects a Unicode value (for more information on Unicode, visit http://unicode.org/). This is done by prefixing the value with 'N' where N stands for National  Language Charter Set. The prefix dictates that the value is a Unicode value. SQL Server provides built in support of Unicode value. If we do not prefix the value with an 'N', the data is interpreted as ASCII value by SQL Server. The following example demonstrates this concept:

>

UPDATE

   Employee

SET

   ArabicName = N '...value...'

WHERE

   ID = ...id...

SQL Server Orphaned Users

Recently I had to deal with what we call SQL Server 'Orphaned Users'. In this post I will talk about what they are and how to fix them. In any database environment, we need to create a Database User with specific privileges (please see my other article on creating a limited SQL Server User). However when we restore the database on another machine, we end up having 'Orphaned Users'.

An orphaned user is the one without matching SID. As you are aware, every object in SQL Server including Users have an SID which uniquely identifies it. This SID is basically a GUID stored in the Master Database. When we restore a database on another machine, the restored User has the SID from the previous location. This doesn't exist on the new machine. Even if we create a Login and associate it with the User, both SIDs are different. This results in Orphaned Users without matching IDs. To fix the above issue, we use the sp_change_users_login stored procedure. This SP has different parameters for different purposes.

To report any orphaned user, the following synxtax is used:

EXEC sp_change_users_login 'REPORT'

Similarly, if the target machine already has a Login with the same name (but different SID), the following will associate the respective User and Login:

EXEC sp_change_users_login 'Auto_Fix', 'Employee'

The above will associate the 'Employee' User with a Login of the same name. Similarly, if we first want to create the Login and then associate it with the respective User, the following synxtax will be used:

EXEC sp_change_users_login 'Auto_Fix', 'Employee', null, 'pwd'

The above will first create a User 'Employee' with Password 'Pwd' and then associate it with the respective User. We can also use the Update_One parameter which will associate a User to an existing Login.

I hope you have learned from this blog. Stay tunned for more...