User ID limit per database, Msg 15065

[Tom Ostberg]

There appears to be a limit of ~16378 user Id's possible per database.
When adding users we eventually get the message:

exec sp_adduser 'testUser', 'testUser', 'user_group'

Server: Msg 15065, Level 16, State 1, Procedure sp_grantdbaccess, Line
160
All user IDs have been assigned.

All of the MSSQL procedures eventually call sp_grantdbaccess which has
the following piece of code:

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
if @uid is null
begin
raiserror(15065,-1,-1)
return (1)
end

so there can not be more than aprox. 16378 users in a database,
don't know why there is this limit, Don't see anything in the groups
talking about it.

Why the limit?
Thanks, Tom Ostberg

[Simon Hayes]

"Tom Ostberg" <to******@eastmtn.com> wrote in message
news:8e**************************@posting.google.c om...

There appears to be a limit of ~16378 user Id's possible per database.
When adding users we eventually get the message:

exec sp_adduser 'testUser', 'testUser', 'user_group'

Server: Msg 15065, Level 16, State 1, Procedure sp_grantdbaccess, Line
160
All user IDs have been assigned.

All of the MSSQL procedures eventually call sp_grantdbaccess which has
the following piece of code:

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
if @uid is null
begin
raiserror(15065,-1,-1)
return (1)
end

so there can not be more than aprox. 16378 users in a database,
don't know why there is this limit, Don't see anything in the groups
talking about it.

Why the limit?
Thanks, Tom Ostberg

Probably 16000+ users per database seemed like a limit no one would ever
reach, because it's so awkward from a management perspective. 2^14 = 16384,
so it's probably not an entirely arbitrary number.

You don't give any background about your security model (especially SQL or
Windows accounts), but using Windows authentication with Windows groups as
logins is one obvious way to reduce the number of users you need to manage,
by shifting the user/group management to the operating system.

This may or may not be a suitable solution for you - if you can clarify why
you need so many users per database (some sort of hosting service?), someone
may be able to suggest an alternative approach.

Simon