While I was writing my earlier blog, I came across another interesting issue with sp_help_revlogin script. In this blog we would learn about the cause of Msg 15419, “Supplied parameter sid should be binary(16)” while transferring the login using sp_help_revlogin and see how to fix it.
Here is my earlier blog about an error raised while using sp_help_revlogin. SQL SERVER – Transfer Logins Error: Msg 15021: Invalid Value Given for Parameter Password
I used sp_help_revlogin to generate create login script on SQL Server A. When I executed on SQL Server B, I received an error
Msg 15419, Level 16, State 1, Line 12
Supplied parameter sid should be binary(16).
Here is the failing query which I had from the first server.
CREATE LOGIN [superman] WITH PASSWORD = 0x0200E5F46BE65CBBE7E043 HASHED, SID = 0x01, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF;
Its clear from the error message that CREATE LOGIN command didn’t like the SID value, but I didn’t supply it manually. It came from an output of sp_help_revlogin.
Later I realized that I rename sa account to Superman and that’s why I am getting 0x01 which is the fixed SID for sa account. If I needed the same account name, then I should rename the sa on destination. Or I can also create new login superman without giving SID value in the command and make it sysadmin.
Reference: Pinal Dave (https://blog.sqlauthority.com)