By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,136 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,136 IT Pros & Developers. It's quick & easy.

User ID limit per database, Msg 15065

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a

"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.