SQL SERVER – Transfer Logins Error: Msg 15419: Supplied Parameter sid Should be Binary(16) – sp_help_revlogin

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;

SOLUTION/WORKROUND

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.