In one of previous posts, Mismatch SQL account SID, you see how to find account SID and create new SQL account with same SID. With that queries you can manually do that, but if you don’t know password, you will still have same problem – incompatible account between SQL servers in cluster. Also, if you need to “replicate” more that one account, that can take a lot of time.

The best solution, in my experience, is creating stored procedure sp_help_revlogin on SQL server. You need to open query window on source SQL server and run query sp_help_revlogin.

After creating stored procedure, you need to run query EXEC sp_help_revlogin. Result will give you list of all account with SID and hashed passwords. (like as example posted bellow)

-- Login: TestUser
CREATE LOGIN [TestUser]
WITH PASSWORD = 0x020025955E0BBC3F702390A3D375A3E0C1DAC37443407347E70F4736252FDBA821C9228D94CFF30997479DEB1AB10B1C7F900ECACAA0CB8F6AB7A3A18016E0999FA450FD9AD0 HASHED,
SID = 0x201932814105274D93494E2B1D55550A,
DEFAULT_DATABASE = [master],
CHECK_POLICY = ON,
CHECK_EXPIRATION = OFF

With these informations, you can create identical account on destination SQL server. Just need to copy output to query window on destination SQL server and execute query.

 

Leave a Reply

Your email address will not be published. Required fields are marked *