SQL Server logins and SIDs

I just spent about 30 minutes debugging something really dumb and I thought I'd post here in case I can save someone that time.

I'm working on some deployment scripts, and part of what they do is create user and group accounts in Active Directory and SQL Server logins for some of those users and groups.

I found myself at a place where I had a login for a group Foo, and I was logged in as a user with that group (I checked with W2K3's convenient whoami /groups to make sure). But for some reason I could not log in to the database. I then created a new login manually for another group, and sure enough that worked fine.

There seemed to be some difference between what my script was doing (create login Foo for windows) and what SQL Management Studio was doing, but here's what was really happening.

When you create a SQL login for a user or a group, the *name* is not used to resolve the login. Rather the SID for the account is used. It turns out that while my AD code was always deleteing and recreating users at each deploy, my database script had code like this:

if not exists(select * from master.sys.syslogins where name='Foo')
begin
  create login [Foo] for windows
end

In other words, I wasn't creating the login if it already existed. So after running the deploy two times, the system was broken - the login didn't get recreated so it was using the old SID (when you delete a user or group account and then add it again, even if you use the same name, it gets a unique SID).

I could see this breaking in other scenarios as well. Just keep in mind that if you delete a user or group and readd it, you need to do the same thing to your SQL logins so they can get the new SID!