November 8, 2001 at 12:51 am
I was under the impression that you could have 32767 users in database but I cannot create more than 16379 users.
Looking at the system proc sp_grantdbaccess, near the end of the code we see:
-- OBTAIN NEW UID (RESERVE 1-4) --
if user_name(5) IS NULL
select @uid = 5
else
select @uid = min(uid)+1 from sysusers
where uid >= 5 and uid < (16384 - 1) -- stay in users range
and user_name(uid+1) is null -- uid not in use
Clearly the creation of a new uid is limited to integers between 5 and 16383. (the first 4 uid's being reserved for the inbuilt database roles.)
I assumed that in being able to have 32767 connections to the database meant that you could have as many users.
Or is there a flag that can be set somewhere that allows me to create up to 32767 users?
Thanks,
Karl Grambow
November 8, 2001 at 8:52 am
Interesting. Never approached this limit (max about 5000 users). I think that above the 16k limit is where groups are stored.
I'll do some checking and try to get back to you soon.
Steve Jones
November 8, 2001 at 2:08 pm
I am curious to see what Steve finds.
Do you have other options? Are you using SQL or NT logins? If you are using NT logins then groups are the way to go for a number of reasons.
November 8, 2001 at 2:44 pm
Few things I noticed, still looking: sysxlogins uses a varbinary to store the sid. therefore there is no
real limit here.
In sp_addlogin, the sid is generated using NEWID(), which doesn't have any numerical limit as this returns a uniqueidentifier.
sp_adduser calls sp_grantdbaccess which leads me back to the beginning.
sp_addrole apparently reserves some space for fixed server roles:
-- OBTAIN NEW ROLE UID (RESERVE 16384-16399) --
if user_name(16400) IS NULL
select @uid = 16400
else
select @uid = min(uid)+1 from sysusers
where uid >= 16400 and uid < (32767 - 1) -- stay in role range
and user_name(uid+1) is null -- uid not in use
From Books Online:
SQL Server allows a maximum of 32,767 user connections.
I cannot find anyplace where the maximum users in a database is listed.
Steve Jones
November 8, 2001 at 9:04 pm
You can create a maximum of 16379 security accounts for a database. The maximum number of roles that you can create for a database is 16367.
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.
Uids 16384 and 16385 are used for the roles db_owner and db_accessadmin, respectively.
The sp_addapprole stored procedure assigns uids in the sysusers system table from a range between 5 and 16383.
Deepak
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply