Supplied parameter sid is in use

Question

I'm trying to copy a user from my live server to my DR server using the following command:

CREATE LOGIN [xxx] 
WITH PASSWORD = 0x010006278DD83306AD19FA8B0CE4405F13DA83DDDFB25B088D7C HASHED
    , SID = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D
    , DEFAULT_DATABASE = [master]
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF

I then get an error:

Msg 15433, Level 16, State 1, Line 1 Supplied parameter sid is in use.

So I check the sys.syslogins with:

SELECT *
FROM sys.syslogins
WHERE sid =  '0xFEEBD63ECD0F6A41AE60A6E0414F5A1D'

This finds nothing. So my question is what else could be using this sid?

Answer 1

As @SteBov noted in his comment, comparing the sid with quotes around it is problematic since that forces an implicit conversion to be performed against the sid column. Since the conversion converts the hexadecimal value into a character value, you will never see the login in the query results.

If you check the execution plan for the following statement:

SELECT *
FROM sys.server_principals sp
WHERE sp.sid = '0xFEEBD63ECD0F6A41AE60A6E0414F5A1D';

We see the following clustered index scan against the internal table, sys.sysxlgns:

In the details, you can see the following predicate:

[master].[sys].[sysxlgns].[type] as [p].[type]<>'M' AND CONVERT_IMPLICIT(varchar(85),[master].[sys].[sysxlgns].[sid] as [p].[sid],0)='0xFEEBD63ECD0F6A41AE60A6E0414F5A1D'

If you run a select changing the CONVERT_IMPLICIT into a simple CONVERT statement, as below, you can see the sid column is not what you'd expect:

SELECT CONVERT(varchar(85), sp.sid, 0)
FROM sys.server_principals sp;

In order to successfully find the login with the duplicate sid, you'll need to run the select like:

SELECT *
FROM sys.syslogins sl
WHERE sl.sid = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D;

Preferably, you'd look at sys.server_principals since syslogins is a SQL Server 2000 compatible view that has been deprecated.

SELECT *
FROM sys.server_principals sp
WHERE sp.sid = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D;