1

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

1 Answer 1

4

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

2
  • 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? Apr 2, 2012 at 8:21
  • Forget my last comment, I took a look at sys.database_principals instead, which has database role. Thanks again. Apr 2, 2012 at 8:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .