We are in a process of upgrading our SQL Server database. We are also ditching the Oracle databases that we had and are migrating all the databases to SQL Server 2016.
The internal db structure is something like, we have two Master databases, which have all the organization and product information. The rest of the web applications access these two databases frequently (mostly read-only).
For web applications, we have one user per application, which is also added in the Master databases (to ensure access of information).
Over the years, many of the web applications are decommissioned and some new ones have been created, (some are in the process of development), so we are also deleting their users and checking others as well.
What I want to inquire is that, what are the drawbacks of having a common user for all web applications? Does it affect performance of the web applications or the DB server? Which one will give me optimum performance and will be better both for the applications and the DB?
1 User for all web apps.
1 User for each web app added to the master databases as well.
Best Answer
The surrogate ID on sys.database_principals is an integer. This suggests a maximum user count of a billion-ish. The Maximum Capacity Specifications for SQL Server mentions no limit on logins or users that I can see. It does say there is a maximum of 32,767 connections at any one time. From any practical point-of-view there's nothing to stop you creating one user per application.
From a performance perspective, I've never seen an issue that resolves to too many user being defined. This is different too many being active. Each active connection consumes a small but finite resource. From what I remember this resource is not shared if different connections have the same user, so nothing to gain there.
(There is such a thing as connection pooling, however. To improve performance all connection requests must be constructed identically, however, including user names. This could work per application (with application-specific users) or per instance (with a shared user). To be effective developers must be meticulous with their connections.)
User credentials and permissions have to be stored somewhere. So, yes, having a ludicrous number of users will consume measurable resource. Since SQL Server is built to handle terabytes of data I doubt this will ever be a limiting factor. Considering how often authorisation is checked I'd imagine the storage and processing code paths have had a lot of optimization attention through the years.
This is just a long way of saying many users equals more resource but you won't be able to measure the downside.
The overwhelming considerations for me are the benefits achieved in security and monitoring by having separate credentials.
A single user, used by all applications, must by definition have access to all the data. If there's a piece of data it doesn't need that data is not used by any application and shouldn't be in this database. That's a security risk. One breach by one bit of one application opens the whole data infrastructure to exploitation. In contrast having separate users which are granted only the SELECT or EXECUTE rights they explicitly require limits the damage any one application failure can expose.
Secondly - monitoring: something, somewhere will consume too much resource. Knowing it's running as
user_genericis not helpful. Knowing it's running asuser_nightly_product_loadwill tell you exactly which code to examine. The complement of this is Resource Governor which allows limiting a connection's access to CPU and memory, if that connection can be distinguished from others. The easiest way is to have separate user credentials.Just for giggles I started a VM with SQL Server 2017 RTM, default installation. I set it to
CREATE USER <user_name> WITHOUT LOGIN. The user names were created from a SEQUENCE. It took a while, and the creation rate decreased over time, but it created over 270,000 users before I gave up. If there is a limit, it's greater than that.