No. of User Accounts in SQL SERVER 2000 and SQL SERVER 2005 ..plz

I want to know the maximum number of users that can be created in SQL SERVER 2005 and the previous version...?

Yorker:

I am going to assume that you will hit the limitations of the "uid" field. This field is a SMALLINT datatype; however, it appears that groups begin @. 16384 and that this field must be non-negative. Therefore, it appears that a user database MIGHT be limited to 16384 distinct users. However, on a serverwide database you can have many databases therefore leading to at least N x 16384 distinct users. There does not look to be any "variable size" limitation on the LOGIN side because this is based now on SIDs whereas in much older versions of sql server it was formerly based on "SUID"s -- The cardinality of the number of SIDs appears to be enormous.

Would someone please check me on this?

Dave

|||The maximum number of logins in 2000 was 35,365, in 2005 it was increased to 65,000 (I think).

However, I would HIGHLY recommend using windows authentication and group membership for rights, instead of 35,000+ SQL logins.|||any evidence to prove your numbers... ? Gentleman ?|||See this: http://msdn2.microsoft.com/en-us/library/ms187376.aspx

This says in 2005 "you can create more than 32,767 users". I was slightly off with my number in 2000. I thought I read somewhere else about the 64k limit in 2005, but I might be mistaken.

I still it is a nighmare to manage 32k logins.|||From http://msdn2.microsoft.com/en-us/library/ms187376.aspx, it infers that SQL2K can only have 32767 SQL users, but SQL2K5 can have more. How much more, I don't know off-hand.

But this doesn't say how many logins there could be (although I would guess it would be the same). http://msdn2.microsoft.com/en-us/library/ms174355.aspx doesn't give many clues.

I would strongly recommend using Active Directory, where the limit doesn't actually apply in the same way. Plus, do you really want to have your DBAs managing this type of thing?

Rob|||But... it turns out that http://support.microsoft.com/default.aspx/kb/303879 quite clearly states that SQL2000 can only have 16K users.

But I guess the types used in sys.sysusers will cater for more.|||

I think that is refering to "database logins" and not "server logins".

The other one does say "This is because, in SQL Server 2005, you can create more than 32,767 users, groups, and roles, and 32,767 data types." This kind of implies, users/groups/roles use the same id counter, so combined you can't have more than 32,767.

MS added more than 16k of users for a reason, although I find it hard to believe someone requested that to be added because they ran out of logins. I have a hard enough time managing 100 SQL logins (excluding AD Groups), I can't imagine the headache I would have with more than that. :)