How many users are too many?

Question

Post reply

How many users are too many?

Aparna-1

SSCrazy

Points: 2339
More actions
August 13, 2010 at 12:15 am

#226024

Hi All,
This question might look silly. I want to know guru's thoughts on the no. of users in a database. we have users table with 6000 users and therefore we have 6000 users and what a user can see in an application is coded in the views.
e.g.
exec as user = 'test1'
select * from companies--can see investment companies
revert
exec as user = 'test2'
select * from companies --can see Mediacompanies
revert
these two queries return different values.
The clients subscribing to our application are increasing day by day and therefor we end up creating more users. Is this is the only approach to ensure row-level security and if so what is the max users that can be created or there are better ways of doing things.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

sqldba4u Hall of Fame Points: 3414 More actions · Answer 1

Pls clarify are there 6000 users existing in ur db or u r taliking abt connections.

happycat59 One Orange Chip Points: 29389 More actions · Answer 2

It is not good practice to add a user for each and every person who needs access to your database. Wherever possible, use AD groups to deal with this. Depending on how you have written your views, they may still work correctly without needing any changes.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

it sounds like an application is probably using a common sql login for group authentication and then the "users" are stored in a table that controls permissions to the application. Something sort of like how asp .net does logons in SQL server.

6000 is really not a whole lot of users. We had a database with 2.5 million users in a similar setup - most of them would connect on a daily basis. The db can handle it when tuned properly and designed well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Aparna-1 SSCrazy Points: 2339 More actions · Answer 4

Thanks everyone for your response.

Jason is spot on. We ave a .NET application that uses a common login for group authentication. Then the sps are called passing the username of the logged in user(Saved in the table). Then the sp gets executed as:

exec as user = 'test1'

--logic of the sp

revert

2.5 million users? If I am following you correctly are there 2.5 million user entries in sysuers table of the database?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

not in sysusers but in the Users table that is used by the .Net app.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Aparna-1 SSCrazy Points: 2339 More actions · Answer 6

That is my problem. I have 6000 users in the "users" table and also an entry for each user in sysusers table. This was the practice in my organization (even before I joined) for achieving row-level security and now I am thinking about a better solution.

example a user test1 is created in the "users" table and a corresponding user gets created as

CREATE USER test1 WITHOUT LOGIN

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

I do have an application in a different environment that has done a similar thing to that. Not only did it create a user in sysusers for each user but also for that users machine. Maintenance is heinous in a setup like that. I don't believe it to be necessary but that is something that will need to be worked out with the dev team.

This other app only has about 1500 user accounts for it. I would change it in a heartbeat if I could. Since we purchased the app, we are limited in ability to change the app.

I think 6000 users is far too many in the setup that you have described. Talk it over with the dev team and find an alternate solution (roles work really well for that. You could have a roles column that contains the role for a specific user. Then drive the security internally in the app for the roles - as an option. Some setups will have an additional table that outlines the permissions for each role.)

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Aparna-1 SSCrazy Points: 2339 More actions · Answer 8

Thanks 6000 seems too many now.

The application has been around for long now (>5 yrs). More than 1,000 Views are all based on this security model.

Yes we have a roles table that governs which user sees what.

we are basing all the development on this security model and that is becoming unmanagable.

All the views are coded based on USER_NAME() function.

Looks like we have a big task ahead