SIDs and IDs
If you are writing code that interacts with the SQL Server engine, you should be aware of two numbers that are used as internal representations of principal. Every principal has an ID and a Security ID (SID). The meaning and source of these numbers depend on how the principal was created, and whether it is a server principal or a database principal.
Server-Level Identification Number
When a SQL Server login is created, it is assigned an ID and a SID. These are visible in the sys.server_principals catalog view as principal_id and SID. The ID (principal_id) identifies the login as a securable within the server. It is assigned by SQL Server when the login is created. When a login is deleted, its ID number is recycled. The SID identifies the security context of the login and is unique within the server instance. The source of the SID depends on how the login is created. If the login is created from a Windows user or group, it is given the Windows SID of the source principal; the Windows SID is unique within the domain. If the SQL Server login is created from a certificate or asymmetric key, it is assigned a SID derived from the SHA-1 hash of the public key. If the login is created as a legacy-style SQL Server login that requires a password, the server will generate a SID.
Database-Level Identification Number
When a database user is created, it is assigned an ID and a security ID (SID). These numbers are visible in the sys.database_principals catalog view as principal_id and SID. The ID identifies the user as a securable within the database. When a database user is deleted, its ID is recycled. The SID assigned to a database user is unique within the database. The source of the SID depends on how the database user is created. If the user is created from a SQL Server login, it is given the SID of the login. If the user is created from a certificate or asymmetric key, the SID is derived from the SHA-1 hash of the public key.
Maximum Number of Database Users
The maximum number of database users is determined by the size of the user ID field. The value of a user ID must be zero or a positive integer. In SQL Server 2000, the user ID is stored as a smallint consisting of 16 bits, one of which is the sign. For this reason, the maximum number of user IDs in SQL Server 2000 is 215 = 32,768. In SQL Server 2005 and later versions, the user ID is stored as an int consisting of 32 bits, one of which is the sign. These additional bits make it possible to assign 231 = 2,147,483,648 ID numbers.
Database user IDs are divided into preallocated ranges, as illustrated in the following table.
SQL Server 2000 ID |
SQL Server 2005 ID |
Allocated to |
|---|---|---|
0 |
0 |
public |
1 |
1 |
dbo |
2 |
2 |
guest |
3 |
3 |
INFORMATION_SCHEMA |
4 |
4 |
SYSTEM_FUNCTION_SCHEMA |
5 - 16383 |
5 - 16383 |
Users, aliases, application roles |
16384 |
16384 |
db_owner |
16385 |
16385 |
db_accessadmin |
16386 |
16386 |
db_securityadmin |
16387 |
16387 |
db_ddladmin |
16389 |
16389 |
db_backupoperator |
16390 |
16390 |
db_datareader |
16391 |
16391 |
db_datawriter |
16392 |
16392 |
db_denydatareader |
16393 |
16393 |
db_denydatawriter |
16394 - 16399 |
16394 - 16399 |
Reserved |
16400 - 32767 |
Roles |
|
16400 - 2,147,483,647 |
Users, roles, application roles, aliases |
The user ID size is increased from smallint (16 bits) to int (32 bits). APIs that require a 16-bit user ID will return incorrect results if passed a 32-bit user ID. When migrating data and applications created for SQL Server 2000 or earlier, you should review code for references to the following deprecated interfaces.
sysusers
syscacheobjects
sysmembers
sysobjects
syspermissions
sysprocesses
sysprotects
systypes
USER_ID
These interfaces require 16-bit user IDs. They do not return correct results when used with 32-bit user IDs.