SQL Server Performance Forum – Threads Archive

Create Login specifying sid

Hi 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.
Mouse

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.

OK I’ve finally worked it out – apparently it takes varbinary(16) but the binary has to be exactly 16 so the following works: create login newguy5 with password=’goodpassword1′, sid=0x2D8F3B9FD9AAC240B9526F50136EF1CE what doesn’t work is when I use a parameter (which had me fooled for awhile) – so the following DOESN’T work. declare @sid as binary(16) set @sid = newid() create login newguy7 with password=’+verogue46′, [email protected] go 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. declare @sid as binary(16) set @sid = newid() exec sys.sp_addlogin ‘newLoginGuy’, ‘arbitaryPassword1’, null, ‘us_english’, @sid go 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=’

‘ />]

]]>