I am creating a product software that many people will install on their machines themselves.
It is a VB.Net - SQL Server application.
I certainly don't want a user to have some SQL Server GUI tool like SQL Server Management Studio installed and then fiddling with the database values / definitions directly.
But when my customer will install the application (and the database - and SQL Server instance if there isn't any) with his own (most probably system admin) account, that account is obviously going to be authorized to access, read and modify the database.
Is there any way to prevent it?
We tried to keep
.mdf file with app, but that can be attached manually.
We tried to use
.sdf file, which allows it's own password to be created, and we loved the idea initially, but that is ok for single project .net solution. For a .net solution with multiple projects (e.g. one for user info and authentication, one for staff payroll etc.), it takes 1 copy of that file for each project, rendering that idea useless.
The standard way to manage application/database permissions is to create database schemas and roles and assign permissions to those roles, such as ReportViewer (with data reader only access), SensitiveDataViewer (for salaries etc) and remove all default access on your database access.
Then you create logins using domain groups (preferably) or users, and add those to roles. If your are on a domain and use domain groups, you can then manage access through Active Directory user membership of groups.
BUT, you can't stop a sys admin looking at/changing data. If you manage access through roles you are shifting the responsibility of security to the system administrator(s), and that's a good thing.
You could encrypt your entire database, but a sys admin could still circumvent this if they really wanted to.
Schema, Owner for objects in MS SQL