Maximum number of database users and roles that you can create (303879)

MORE INFORMATION

All permissions and ownership of objects in a database are controlled by the user account. Roles are a mechanism that allow you to collect users into a single unit against which you can apply permissions.

In each SQL Server 7.0 and SQL Server 2000 database, there is a sysusers system table. The uid column in the sysusers system table contains a unique smallint value for each Microsoft Windows user, Windows group, Microsoft SQL Server user, or SQL Server role in the database. For example, the uid value of dbo, guest, and INFORMATION_SCHEMA are 1, 2, and 3, respectively. Uids 16384 and 16385 are used for the roles db_owner and db_accessadmin, respectively.

You use the sp_grantdbaccess stored procedure to add a security account in the current database for a Microsoft SQL Server login or Microsoft Windows user or group. The sp_grantdbaccess stored procedure also enables the security account so that you can grant permissions to the account to perform activities in the database.

You can also use the sp_adduser stored procedure to add a security account for a new user in a database. The sp_adduser stored procedure is included with SQL Server 7.0 and SQL Server 2000 for backward compatibility, although use of sp_grantdbaccess is preferred.

Both the sp_grantdbaccess and sp_adduser stored procedures assign uids in the sysusers system table from a range of 5 to 16383. Thus, you can add a maximum of 16379 security accounts to a database by using sp_grantdbaccess or sp_adduser.

You use the sp_addrole stored procedure to create a new role in the current database. The sp_addrole stored procedure assigns uids in the sysusers system table from a range of 16400 to 32766. Therefore, you can create a maximum of 16367 roles for a database by using the sp_addrole stored procedure.

An application role is a special type of role that is used for application security. You use the sp_addapprole stored procedure to add application roles. The sp_addapprole stored procedure assigns uids in the sysusers system table from a range between 5 and 16383.

The sp_addalias stored procedure is provided with SQL Server 7.0 and SQL Server 2000 for backward compatibility. You use the sp_addalias stored procedure to map a login to a user in a database. The sp_addalias stored procedure assigns uids in a range of 5 to 16383.