467,074 Members | 933 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,074 developers. It's quick & easy.

User ID limit per database, Msg 15065

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
Jul 20 '05 #1
  • viewed: 2459
Share:
1 Reply

"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
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Tom Ostberg | last post: by
1 post views Thread by JESUS | last post: by
3 posts views Thread by Michael Glaesemann | last post: by
13 posts views Thread by Mickey | last post: by
3 posts views Thread by =?Utf-8?B?QmlsbHkgWmhhbmc=?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.