Monday, March 25, 2013

PowerShell to convert SID to User name and vice versa

Recently when we were profiling for database performance using SQL Profiler, we could see some query hits are coming to DB server from an unknown user account. Unknown means the user name in the profiler result view,it shows the SID of the user instead of readable username. Initially we ignored those requests and the performance issues got resolved by tuning the SPs.

The story was over with success in the task. But one item got into my backlog. Its nothing but how to convert the sid to user name. SID is nothing but the secure identifier given to windows system objects. Initial google gives a command called wmic and the usage is as follows.

wmic useraccount get name,sid

I tried this command and it lists all the users in the active directory. Very difficult to figure out. I had to export   to excel and filter out. This thinks me to code and as always I selected C#.Net. It was so simple.

        static string GetUserNameFromSID(string sid)
            SecurityIdentifier si=new SecurityIdentifier(sid);
            NTAccount ntAccount=(NTAccount)si.Translate(typeof (NTAccount));
            return ntAccount.Value;

I created this program in my machine and it needs additional one more step of copying to the server and to make sure the server has the corresponding .net version. I forgot PowerShell again. This should have been done in PowerShell. Ported to PowerShell immediately and saw its good.

# Program to find windows user name from sid
  $sid="<sid string value>"
  $sidInstance = New-Object System.Security.Principal.SecurityIdentifier($sid)
  $ntAccount= $sidInstance.Translate([System.Security.Principal.NTAccount]);

Is there a program required to find out the user name from sid? How the system admins are doing it? The chance of them coding to get the username from the sid is very less. So there must be a direct command. This directed me again to the wmic command. After some reading I could see that wmic is the command interface for our favorite WMI (Windows Management Instrumentation). It supports filtering. Yes getting user name from sid is simple as the below command which can be directly entered in command prompt.

wmic useraccount where sid="S-1-5-21-1411863409-2385935799-2106976

The take out from this experience is "Commands itself becoming programs". If we look at the command we can see there is a where condition :-)

Happy coding.

No comments: