Search code examples
sql-serveractive-directorysid

Edit binary data field

I have a database that saves the SID of a Windows AD user in a varbinary datatype. I can view the SID string by using the statement CONVERT(nvarchar(MAX), id, 2) AS SID to confirm. The user account was deleted and recreated, so everything is the same except for the SID. I have the correct SID and would like to edit it. Can't do it with the software, cause it doesn't support it. Is there any way to do this?

Solution

  • Used the following, since it was my own user:

    UPDATE myTable 
SET id = CAST(suser_sid() AS VARBINARY(MAX))
where [UserName] = 'domain\myuser'

    Compared other orphaned tables with the following using my old SID:

    where [User_SID] = SID_BINARY(N'S-1-5-55-54444')