Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are thinking of using sql users / roles for row level security.

I have read that the max is approx 32,000+/- users, because the sys.users has a smallint id.

However I've not been able to find anything absolutely conclusive.

n.b. I'm not after the number of concurrent users, but the max number of registered users.

thanks for the help

share|improve this question

1 Answer 1

up vote 4 down vote accepted

sys.sysusers is a depreciated view for SQL Server after SQL2000. In 2005+ it reports the uid column is a small int to keep backwards compatibility, but the underlying id column is an int. sys.sysusers notes that anything above 32,767 will return null instead.

http://msdn.microsoft.com/en-us/library/ms179871.aspx

The newer sys.database_principals and sys.server_principals views shows the principal_id column as an int. So you would be good until 2 billion registered.

http://msdn.microsoft.com/en-us/library/ms188786%28v=sql.105%29.aspx

share|improve this answer
    
Great answer @ghoerz, thanks! Just to make sure, is the same for database roles as well? I can see on sys.server_principals that type can be set to "server role", but guessing this is different to database role? –  Alex Key Apr 2 '12 at 8:21
    
Forget my last comment, I took a look at sys.database_principals instead, which has database role. Thanks again. –  Alex Key Apr 2 '12 at 8:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.