Practical limit to number of SQL Server logins?

Question

Working with an application that needs to provide row and column level security for user reports. The logic for the row filtering an column masking is in place, but there are still decisions to be made about identifying users at report execution time.

The application uses a single SQL Server login to authenticate, as all rights are data driven within the application itself. This mechanism does not carry well to reports, as clients like Crystal and MS Office do not authenticate through the application (web and WinForms).

The traditional approach of using SQL Server logins and database users will work will, but may have one issue. In some implementations of the application, the number of users who run reports and need to be uniquely identified may run into the hundreds.

Are there any practical limits to the number of logins or users on a SQL Server database (v 2005+) where this approach may cause problems? Administration of the users on the database server can be automated by the application, but the potential number of credentials may be a concern.

We have looked into user impersonation techniques, but they become difficult to implement when a report client such as Excel authenticates directly to the server.

Edit: The concern is not concurrency or workload, but rather administration issues on remote instances where a local DBA is not available, especially when the server is not dedicated to the application. Interested in scenarios where the numbers of logins were problematic.

Answer 1

I've used your described approach (SQL Server accounts managed automatically by our application) and we didn't have any trouble. However, we only ever had a maximum of perhaps 200 SQL accounts. But we didn't experience any kind of administrative overhead except when "power users" restored databases without telling us, causing the SQL login account to become out of synch with the database*.

I think your approach is sound.

EDIT: Our solution for this was a proc that simply ran through the user accounts and called our procs that deleted/created the user accounts. When the power users called this proc all was well, and it was reasonably fast.