SQL Server Performance

Create Login specifying sid

Discussion in 'SQL Server 2005 General Developer Questions' started by mouse.catcher, Oct 11, 2006.

  1. mouse.catcher New Member


    I have been trying to create a login for sql server 2005 and specify the SID but I just can't seem to get it to work. So far I have:

    CREATE LOGIN newguy WITH PASSWORD='password', SID=1234

    all the values I try for the sid seem to fall flat on their face although I'm pretty sure its a varbinary.

    any help would be appreciated.

  2. thomas New Member

    SID is varbinary(85). It's a GUID. Try doing a

    select SID from syslogins

    get one of the existing GUIds and modify it slightly, and try that? I've never done it or needed to, but that's what I'd try.
  3. mouse.catcher New Member

    OK I've finally worked it out - apparently it takes varbinary(16) but the binary has to be exactly 16 so the following works:<br /><br />create login newguy5 with password='goodpassword1', sid=0x2D8F3B9FD9AAC240B9526F50136EF1CE<br /><br />what doesn't work is when I use a parameter (which had me fooled for awhile) - so the following DOESN'T work.<br /><br />declare @sid as binary(16)<br />set @sid = newid()<br />create login newguy7 with password='+verogue46', sid=@sid<br />go<br /><br />That wasn't much good to me because I was recreating my test database whenever there was a schema change and for my unit tests I needed to know the users SID (data is accessed by views filtered by the users SID). In the end I used the system stored procedure to pass in a parameter as follows.<br /><br />declare @sid as binary(16)<br />set @sid = newid()<br />exec sys.sp_addlogin 'newLoginGuy', 'arbitaryPassword1', null, 'us_english', @sid<br />go<br /><br />As usual its pretty simple when you know how - I only post the solution to save some other poor sucker some time [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page