Create login with SID as string

I'm trying to create a login as follows:

CREATE LOGIN [Test] WITH PASSWORD='Test123', SID=SID_BINARY(N'S-1-5-21-408552231-458724953-3089381293-513')

But no matter what it looks like SID only accepts a binary input, no subqueries or anything else really.

Incorrect syntax near 'SID'.

Is there a way to create a login with SID with a string?

A funny thing about SID_BINARY() - it outputs binary(28) in this case, whereas CREATE LOGIN expects binary(16). As Solomon points out, and adding here so there is no ambiguity, this means there is no way to create a SQL Server SID based on - or that can map to - the Windows identifier you posted in your question. You want CREATE LOGIN ... FROM Windows for that.

Your first problem, though, is the syntax error. The reason you're getting that is because you can't use expressions in a CREATE LOGIN statement, and it doesn't take variables either, so you need to construct dynamic SQL.

I tried to use all 28 bytes as follows:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

DECLARE @sql nvarchar(max) = N'CREATE LOGIN [Test] WITH PASSWORD = N''foo'',
  SID = ' + CONVERT(varchar(64), SID_BINARY(@sid_in), 1) + N';';

PRINT @sql;

Yielded:

CREATE LOGIN [Test] WITH PASSWORD = N'foo', 
  SID = 0x01050000000000051500000027035A185996571BAD3724B801020000;

Of course, executing that SQL yielded an error, as I expected:


Msg 15419, Level 16, State 1

Supplied parameter sid should be binary(16).

So then I wondered if truncating to 16 bytes would work. I thought this was incompatible because losing 12 bytes should be a problem:

DECLARE @sid_in varchar(100), @sid_out varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

SELECT original = SID_BINARY(@sid_in);
SELECT trimmed  = CONVERT(binary(16), SID_BINARY(@sid_in));

Results:

original
----------------------------------------------------------
0x01050000000000051500000027035A185996571BAD3724B801020000

trimmed
0x01050000000000051500000027035A18

But then I tried it, and it (kind of) worked. Here is an example:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

DECLARE @sql nvarchar(max) = N'CREATE LOGIN [Test] WITH PASSWORD = N''foo'',
  SID = ' + CONVERT(varchar(64), CONVERT(binary(16),SID_BINARY(@sid_in)), 1) + N';';

EXEC sys.sp_executesql @sql;

And to prove that trimming those 12 bytes off has little effect:

DECLARE @sid_in varchar(100);
SET @sid_in = 'S-1-5-21-408552231-458724953-3089381293-513';

SELECT SUSER_SNAME(CONVERT(binary(16),SID_BINARY(@sid_in)));

Results:

----
Test

This just means that you can write your code in such a way that you can still get to the right login given the Windows S-1... identifier. But there is a side effect of this trimming that may or may not be important to you. It is impossible to get back to the original SID you passed in because some of that data is gone. So if you create the functions @eckes points out above, you will find slightly different output than you would expect:

SELECT name, sid, ws1 = dbo.fn_SIDToString(sid) 
  FROM sys.server_principals 
  WHERE name = N'Test';

Results:

name    sid                                   ws1
----    ----------------------------------    ------------------
Test    0x01050000000000051500000027035A18    S-1-5-21-408552231

That may or may not be acceptable. I do not know enough about Windows identifiers or about exactly why you want to create a SQL auth user that is appears on first glance to be mapped to one to know if this will meet your needs. But it gets past both the syntax error and the 28/16 issue.

Function I used, included here in case the link dies:

CREATE FUNCTION [dbo].[fn_SIDToString]
(
  @BinSID AS VARBINARY(100)
)
RETURNS VARCHAR(100)
AS BEGIN

  IF LEN(@BinSID) % 4 <> 0 RETURN(NULL)

  DECLARE @StringSID VARCHAR(100)
  DECLARE @i AS INT
  DECLARE @j AS INT

  SELECT @StringSID = 'S-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 1, 1)))) 
  SELECT @StringSID = @StringSID + '-'
     + CONVERT(VARCHAR, CONVERT(INT, CONVERT(VARBINARY, SUBSTRING(@BinSID, 3, 6))))

  SET @j = 9
  SET @i = LEN(@BinSID)

  WHILE @j < @i
  BEGIN
    DECLARE @val BINARY(4)
    SELECT @val = SUBSTRING(@BinSID, @j, 4)
    SELECT @StringSID = @StringSID + '-'
      + CONVERT(VARCHAR, CONVERT(BIGINT, CONVERT(VARBINARY, REVERSE(CONVERT(VARBINARY, @val))))) 
    SET @j = @j + 4
  END
  RETURN ( @StringSID ) 
END

StackExchange Database Administrators Q#230432, answer score: 4