2

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.

2 Answers 2

4

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_generic is not helpful. Knowing it's running as user_nightly_product_load will 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.

2
  • Thank you for a detailed explanation. I think it also is a matter of the DBA being active. The previous DBA here wasn't much interested in 'administering' as a result, many users which are not required are still there in the database.
    – progrAmmar
    Dec 11, 2017 at 23:57
  • At my place, where a ton of legacy applications are still running havoc, the method we choose to use (Securest vs. lowest maintenance) is to use Active Directory groups (sql_appX, sql_appY), each group having specific security in SQL Server (using dbRole_appX) depending on the needs of the application using it. You then assign/unassign users into theses AD Groups following personnel movement (hire/fire/change assignation). Applications are developed using TRUSTED_CONNECTION parameter. This way, you see the actual AD username in the Profiler or WhoIsActive script to know who is busting your DB.
    – Philippe
    Dec 12, 2017 at 20:06
1

The big risk of using just 1 account is access. Do your finance users need the same access as your purchasing system? Does your sales app need access to the payroll database? If they do need access, do they need the same access? E.g. maybe you read data from your finance database in your sales app, but does it need to write back to it?

What if you have a 3rd party app that needs to be db_owner of it's database. Do you want all apps to be db_owner of that database?

What if a developer passes the password out to someone they shouldn't have and they want to change the password. You'll have to update all of the apps.

Imagine if 1 product has a SQL injection problem. If that user has read/write to every database then all of your data can be read by exploiting that 1 problem. If you have different users for each app then the app can only exploit the data that it should be access anyway (still a problem, but the scope is reduced.

The general best practice is to provide the least level of access possible.

I've had good experiences with apps running as Managed Service Accounts, using integrated security to access just the databases that they need access to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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