Prevent Database Restore

Question

Post reply

Prevent Database Restore

mobasha

SSCertifiable

Points: 5334
More actions
April 23, 2008 at 12:42 am

#189591

hi all.
how to prevent a dbowner from restoring a database??
..>>..
MobashA

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Adiga One Orange Chip Points: 27284 More actions · Answer 1

I think it is not possible as the DB Owner has full access on the database.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 2

It depends on whether the user is the owner, or is in the db_owner group. From BOL:

If the database being restored does not exist, the user must have CREATE DATABASE permissions to be able to execute RESTORE. If the database exists, RESTORE permissions default to members of the sysadmin and dbcreator fixed server roles and the owner (dbo) of the database (for the FROM DATABASE_SNAPSHOT option, the database always exists).
RESTORE permissions are given to roles in which membership information is always readily available to the server. Because fixed database role membership can be checked only when the database is accessible and undamaged, which is not always the case when RESTORE is executed, members of the db_owner fixed database role do not have RESTORE permissions.

EdVassie SSC Guru Points: 60472 More actions · Answer 3

You should only give db_owner rights to users that can be trusted and need to have db_owner functionality to do their job.

If you have given db_owner authority to more people than need it, you need to hange their authorities to be only what they need.

If you are not sure what rights they do need, check in BOL. If you are still not certain, ask this forum and we may be able to help.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

mobasha SSCertifiable Points: 5334 More actions · Answer 4

i was wondering if i could create like a trigger to prevent a restore over the database.

..>>..

MobashA

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 5

There are DDL Triggers that can protect objects - but not sure if a RESTORE would be covered or if this type of trigger could be applied at the DB level?

Thanks

Kevin

Adiga One Orange Chip Points: 27284 More actions · Answer 6

Yes Kevin you are right. You can only create triggers for INSERT, UPDATE, DELETE statements.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

mobasha SSCertifiable Points: 5334 More actions · Answer 7

there is some thing called DDL triggers but i couldnt find some thing related to restore or backup.

..>>..

MobashA

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 8

in BOL there is a way to protect tables from being dropped - maybe this can be tested for restore operations?

CREATE TRIGGER safety

ON DATABASE

FOR DROP_TABLE, ALTER_TABLE

AS

PRINT 'You must disable Trigger "safety" to drop or alter tables!'

ROLLBACK

;

instead of ON DATABASE - you can use ON SERVER and check if there is a DROP_DATABASE or RESTORE???

I will investigate in a bit - have a few others tasks on the go

Thanks

Kevin

mobasha SSCertifiable Points: 5334 More actions · Answer 9

i did tried so but no it didnt worked.

am thinking off creating a snapshot for the database, this should do the job right?

..>>..

MobashA

Kevin-458339 SSCrazy Points: 2717 More actions · Answer 10

I am not too sure what will hapen to the snapshot when a restore over the database is attempted?

Kevin

mobasha SSCertifiable Points: 5334 More actions · Answer 11

i just tryed it when try to restore sql generate an error msg, and u cant restore untill drop the snapshot first

..>>..

MobashA