Export (0) Print
Expand All

IDs and SIDs

New: 14 April 2006

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.

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 an SID.

When a database user role 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, 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

In SQL Server 2005 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 an earlier release of SQL Server, 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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft