Maximum Number of Database Users and Roles

I unfortunately found out during a late night update that the maximum number
of security accounts for sql server is 16379
(http://support.microsoft.com/?id=303879).
I have a web site that I had/have to change the security model for which was
originally setup using a Windows Domain Group and users to access our sql
server database. We were using mangled url's to pass the username and
password to our site, which was fine, but recently, MS removed the ability
to mangle the url. Our customers want the ability to pass their users from
their site to our site seemlessly without any pop-up dialog (i.e. basic
auth). One of our clients have approximate 40,000 users that need to be
setup for the site. We are trying not to rewrite hundreds of stored procs
to add the username and password parms, so we are kinda in a bind.
How are you accessing your database using sql secrutiy accounts with > 16379
users? Each user has to have a unique login, and we would like to use a
built in security model, but we cannot use the Windows domain groups.
Thanks,
BarryDo you have a "lest level of privileges" and can use the guest user for
that?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:Oasd4VtBEHA.1128@.TK2MSFTNGP11.phx.gbl...
> I unfortunately found out during a late night update that the maximum
number
> of security accounts for sql server is 16379
> (http://support.microsoft.com/?id=303879).
> I have a web site that I had/have to change the security model for which
was
> originally setup using a Windows Domain Group and users to access our sql
> server database. We were using mangled url's to pass the username and
> password to our site, which was fine, but recently, MS removed the ability
> to mangle the url. Our customers want the ability to pass their users
from
> their site to our site seemlessly without any pop-up dialog (i.e. basic
> auth). One of our clients have approximate 40,000 users that need to be
> setup for the site. We are trying not to rewrite hundreds of stored procs
> to add the username and password parms, so we are kinda in a bind.
> How are you accessing your database using sql secrutiy accounts with >
16379
> users? Each user has to have a unique login, and we would like to use a
> built in security model, but we cannot use the Windows domain groups.
> Thanks,
> Barry
>|||We need unique way of retrieving information for each and every user, so if
we stay with sql auth, our limit is 16K + users. The only other way around
that is rewritting the hundreds of sp's and web pages to handle passing
around a userid and/or password which we are trying to avoid.
Thanks,
Barry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6rceo0BEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Do you have a "lest level of privileges" and can use the guest user for
> that?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Barry" <no_one@.home.net> wrote in message
> news:Oasd4VtBEHA.1128@.TK2MSFTNGP11.phx.gbl...
> number
> was
sql
ability
> from
procs
> 16379
>|||They will still have their own unique login, even if a number of them uses
the same user name. This means that functions like SYSTEM_USER will return
unique names for each person. I looked in sp_addlogin, and I did not find a
check regarding how many logins there exists.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:uZRTEK3BEHA.3284@.TK2MSFTNGP09.phx.gbl...
> We need unique way of retrieving information for each and every user, so
if
> we stay with sql auth, our limit is 16K + users. The only other way
around
> that is rewritting the hundreds of sp's and web pages to handle passing
> around a userid and/or password which we are trying to avoid.
> Thanks,
> Barry
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:O6rceo0BEHA.2348@.TK2MSFTNGP09.phx.gbl...
which
> sql
> ability
basic
be
> procs
a
>|||I guess I don't understand what you mean by "lest level of privileges" (do
you mean "least"). Are you saying that everybody accesses generic parts of
the sites using a generic username/password, but access their stored info
using their unique username/password?
Unfortunately, almost every page requires a unique identifier for a user for
all of our sites. Everything is customizable, so we have to know who the
user is from everything from a welcome page to specialized list of products
that they work with. Without re-writing hundres of web pages and stored
procs, I don't see how this can be done with sql server auth with > 16K+
users.
The docs for sp_addlogin don't state a limit, I had to search the ms website
for this info:
http://support.microsoft.com/?id=303879
Thanks,
Barry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eLobfo3BEHA.1544@.TK2MSFTNGP09.phx.gbl...
> They will still have their own unique login, even if a number of them uses
> the same user name. This means that functions like SYSTEM_USER will return
> unique names for each person. I looked in sp_addlogin, and I did not find
a
> check regarding how many logins there exists.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Barry" <no_one@.home.net> wrote in message
> news:uZRTEK3BEHA.3284@.TK2MSFTNGP09.phx.gbl...
> if
> around
> in
for
maximum
> which
our
and
users
> basic
to
> be
>
use
> a
groups.
>|||'what I'm trying to say is that each person can have their own login. For
persons who requires special access rules, you create their own users in the
database.
But for the number of persons who can use the least level of privileges, you
don't create users in the database, you create only one guest user instead.
Is the number/percentage users who can use this least level of privileges is
high, you can cut down a substantial numbers of users you have to create in
the database and possibly get under the approach 16000 user limit.
It is important that you understand the difference between a login and a
database user. Also, the KB article you refer to documents the limit of
database users, not SQL Server logins!
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:%23kE6KC6BEHA.2380@.TK2MSFTNGP10.phx.gbl...
> I guess I don't understand what you mean by "lest level of privileges" (do
> you mean "least"). Are you saying that everybody accesses generic parts
of
> the sites using a generic username/password, but access their stored info
> using their unique username/password?
> Unfortunately, almost every page requires a unique identifier for a user
for
> all of our sites. Everything is customizable, so we have to know who the
> user is from everything from a welcome page to specialized list of
products
> that they work with. Without re-writing hundres of web pages and stored
> procs, I don't see how this can be done with sql server auth with > 16K+
> users.
> The docs for sp_addlogin don't state a limit, I had to search the ms
website
> for this info:
> http://support.microsoft.com/?id=303879
> Thanks,
> Barry
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:eLobfo3BEHA.1544@.TK2MSFTNGP09.phx.gbl...
uses
rn
find
> a
so
passing
wrote
> for
> maximum
> our
> and
> users
> to
stored
with
> use
> groups.
>|||Tibor,
The unfortunate part is that there really isn't a place on the site that I
can fit a general user in, I have to know on almost each and every page who
they are. Each user has their own saved items, and each section of our site
has specific items that are unique to that user.
I understand that there is a difference in the # of logins and the number of
database users, it's the number of database users that are putting me in a
bind.
Thanks,
Barry
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ONLI8d6BEHA.3928@.TK2MSFTNGP11.phx.gbl...
> 'what I'm trying to say is that each person can have their own login. For
> persons who requires special access rules, you create their own users in
the
> database.
> But for the number of persons who can use the least level of privileges,
you
> don't create users in the database, you create only one guest user
instead.
> Is the number/percentage users who can use this least level of privileges
is
> high, you can cut down a substantial numbers of users you have to create
in
> the database and possibly get under the approach 16000 user limit.
> It is important that you understand the difference between a login and a
> database user. Also, the KB article you refer to documents the limit of
> database users, not SQL Server logins!
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Barry" <no_one@.home.net> wrote in message
> news:%23kE6KC6BEHA.2380@.TK2MSFTNGP10.phx.gbl...
(do
> of
info
> for
the
> products
> website
> in
> uses
retu
> rn
> find
user,
> so
> passing
> wrote
user
for
access
username
the
(i.e.
need
> stored
bind.
> with
to
>|||You can still se who the *person* is by looking at the login name instead of
the user name!
For instance, the SYSTEM_USER function name returns the login name while the
SESSION_USER function returns the user name.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Barry" <no_one@.home.net> wrote in message
news:esU8GD7BEHA.1484@.TK2MSFTNGP12.phx.gbl...
> Tibor,
> The unfortunate part is that there really isn't a place on the site that I
> can fit a general user in, I have to know on almost each and every page
who
> they are. Each user has their own saved items, and each section of our
site
> has specific items that are unique to that user.
> I understand that there is a difference in the # of logins and the number
of
> database users, it's the number of database users that are putting me in a
> bind.
> Thanks,
> Barry
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:ONLI8d6BEHA.3928@.TK2MSFTNGP11.phx.gbl...
For
> the
> you
> instead.
privileges
> is
> in
> (do
parts
> info
user
> the
stored
16K+
wrote
them
> retu
> user,
way
> user
> for
> access
> username
> the
their
> (i.e.
> need
> bind.
accounts
like
> to
>