What I learned today – GID in sysusers table

I had a question from a colleague yesterday about the GID field in the sysusers table. He had a SQL 2000 database that he had moved to a SQL 2005 instance. When he did this, his third party application stopped working. When he investigated, he noticed that the app was using the GID field in sysusers to validate logins and set roles. After he restored the database on the new server the GID field was 0 for everyone. At this point no one could connect to the database through the app.

In SQL 2000, when you add a user to a database, the GID is 0 for the public role. If you add them to a database role, the GID becomes the UID of the role. I’m not sure what happens if you add a user to more than one database roles, that’s something I’ll have to check out. In SQL 2005 this has changed. The GID for all database users is 0. This is not a field that you can update.  

This is a good example of why you should never depend on the system tables being static. Microsoft always warns that they may be updated when you apply patches or when you upgrade.