backup sql server 2005

Question

backup sql server 2005

bryan van ritter

SSCrazy

Points: 2270
More actions
April 7, 2010 at 4:07 am

#222925

Hi,
I have some strange thing going on. There is a user in our database who can make backups of the database. This user is NO member of the sysadmin and db_backupoperator. This user only has the following rights:
db_owner / public / datareader & datawriter. Am i overlooking something here?
kind regards,
Bryan

Viewing 8 posts - 1 through 7 (of 7 total)

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

From technet: http://technet.microsoft.com/en-us/library/cc966495.aspx

Any logon that requires permissions to perform backup or restore operations should be provided membership in the following SQL Server roles:
Server Role : sysadmin
DB role : db_backupoperator, dbo_owner
Permissions required for performing restore -
Server role : sysadmin, dbcreator
DB role : db_owner

Can u check if that user has any of these roles assigened to him?

Pradeep Singh

bryan van ritter SSCrazy Points: 2270 More actions · Answer 2

Hi Pradeep,

the user has db_owner rights. isn't there a way besides taking away db-owner rights to prevent the user from making backups.

bryan

ps. One Orange Chip Points: 29422 More actions · Answer 3

I guess no. db owner will have sufficient rights to perform backup restores.

Instead, find out what all privilages that particular user requires, give him those specific rights and remove db owner permission from him.

Pradeep Singh

bryan van ritter SSCrazy Points: 2270 More actions · Answer 4

Hi Pradeep,

thanks for your answer. i will do that for sure.

kind regards,

bryan

bryan van ritter SSCrazy Points: 2270 More actions · Answer 5

Hi pradeep,

another question. is there a way to suggest a user has db-owner rights, to make a trigger which prevents the user from doing a backup/restore or drop from de database?

bryan

ps. One Orange Chip Points: 29422 More actions · Answer 6

bryan van ritter (4/7/2010)
Hi pradeep,
another question. is there a way to suggest a user has db-owner rights, to make a trigger which prevents the user from doing a backup/restore or drop from de database?
bryan

U can user DENY Backup database to that particular database user.

I've not tried that before.

This link should help you out...

http://msdn.microsoft.com/en-us/library/ms177518.aspx

From the list i didnt see that u can deny restore but u can deny backup database and backup log for sure for a user having dbo rights.

try that out and let us know the outcome.

Pradeep Singh

bryan van ritter SSCrazy Points: 2270 More actions · Answer 7

bryan van ritter

SSCrazy

Points: 2270

April 7, 2010 at 8:21 am

#1147367

thanks again.

bryan