Software need db owner permission to master database (sql2016)

Question

We have a manufacturer whose software requires authorization for the master database (only db owner working). Unfortunately, the manufacturer can not tell us why this access to the master database is necessary. Without this permission, the application will not start.

What are your opinions? Have you ever seen such software? We have 200 different applications but this is the first one that needs permanent access

Is there any objection to security?

As an alternative, we can install an SQL Express instance because the database is very small. If access to the master DB from your point of view is not a good idea.

Answer 1

I have seen software that requires a sysadmin server role membership, at least during installation. It might be that the software creates dbo-owned objects in master and db_owner is the easiest way to allow that without resorting to sysadmin role membership. Although it might be possible to provide more granular permissions, application code changes might be needed and one cannot the grant the minimal permissions necessary without knowing specifically what the software does and the vendor would need to support such changes.

A separate instance/machine (including in Azure) can help mitigate security concerns. SQL Server Express might be an option to reduce costs if you can work within the resource limitations but be aware your HA options will be limited.

Answer 2

Arguably this should never be the case, but I've seen a lot of vendors require surprisingly high levels of access to the instance in order to function properly. Sometimes they didn't really need it and sometimes they did, but often times the vendor won't support it if you purposefully ignore their setup instructions. And things could easily change with any product update, so you run the risk of future issues if you don't heed their requirements.

• If you're truly curious you could use Extended Events to see what the application login is actually doing.
  • After this, you could try creating a role with the minimal permissions needed based on the activities being performed - but this is somewhat risky in my opinion.
• Agree with Dan that a separate instance (Azure, Express) is definitely ideal if it can fit your needs

In these types of scenarios, I've always found that the business will happily sign a security exception/waiver that can help remove you from being responsible for the potential security gap.

Answer 3

We had a similar scenario and what done is - Grant required permissions, Setup SQL Audit/XEvents, Monitor the installation, Identify the objects created, Run the trace initially for some application functionality and capture the accessed objects in master, revoke privileged access and grant required (read) access. In our case, the app is reading data from some performance DMV's in master. I know, this is a long process but that helps to secure the master DB. As said by Dan Guzman, SQL Express has it's own limitations - DB Size, Memory, No SQL Agent, No HA, No Profiler.

Answer 4

We had a serious debate about actually using CREATE ASSEMBLY ... in one of our products. Had we done so, every update would have required dba level access. Some people really do design so that the application does database migrations on launch rather than during the installation procedure of the update. This in turn would require the application to have dba level access.

We ended up not using it for unrelated reasons.

Answer 5

GRANT VIEW ANY DEFINITION TO was the solution. Now the Application starts withouth db owner role.