I am looking for a script to Restrict Database owner to take database backups in SQL server. Till now i was using Sp_msforeachdb to create role and adding DENY Full and LOG backups to it.
Asked
Active
Viewed 541 times
1
-
2And the problem with that approach is...? – Jeroen Mostert Feb 01 '17 at 21:50
-
if your need is to deny this operation on all databases then using sp_msforeachdb is not a bad idea :) – Hadi Feb 01 '17 at 22:08
-
You might take a peek at this though before you continue using sp_msforeachdb. It will sometimes skip databases. https://sqlblog.org/2018/10/22/sp-ineachdb-2 – Sean Lange Feb 01 '17 at 22:13
-
One remark: denying permission to backup for people who are in the `db_owner` role only prevents accidents. It will not prevent someone in that role from simply resetting permissions to allow it anyway. If someone should definitely not be able to make a backup as a security demand, they shouldn't be an owner. – Jeroen Mostert Feb 01 '17 at 22:30