Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
We have a 3° party application, with an application user who needs to be db_owner on the database (not my choice) on SQL Server system. All stored procedures are encrypted in this database. At some point they do a shrink of the database files in this application, and they do it every day. I want to prevent this, and I was looking if there was a way to do this either with:
Can someone help me
We have an application, with an application user needs to be db_owner on the database
If this application is written "in-house" then push back - hard - and get them to justify why this needs to be the case.
Running an application as a database owner, you might as well hand the database back to the Development team because they can do pretty much whatever they like, with or without your knowledge and/or agreement.
If they're going to break it, let them take the flak and fix it.
If this application is a Package from a third-party supplier (and this is remarkably common), then you're a bit stuck. They can code the application to do whatever they want and again, there's nothing much that you can do about it.
At some point they do a shrink of the database files in this application, and they do it every day. I want to prevent this ...
Why?
Is the shrinking operation causing an operational problem, i.e. causing difficulties for Users/the Business? If so, then that's a very strong argument for getting them to stop doing this.
Another, lesser, argument might be if the "shrunk" data files very quickly grow back to their previous size. IF so, then the shrinking is a waste of time and is probably causing performance issues while the data files grow back again, because growing a data file is always slower than just using free space that's already there.
The only thing I can think of is to create a on delete trigger on each table to raise an exception or soft-delete (e.g. set a deleted column to true). I don't think there is a way to revoke a privilege to an object created by the schema owner--I couldn't get it to work on Oracle. If rows cannot be deleted, then the database files won't shrink. Of course, rows won't be deleted, which may cause other problems for the application.
The bigger issue is that the application is running as the schema owner. That is just bad security practice as it violates the least-privilege principle.
